 |
|
|
06-28-2010, 12:53 PM
|
return count from SQL
|
Posts: 173
|
I have created the following code that queries the db and should return a count; however, for some reason I am getting 0. If I put the query directly into the db I get a approriate response.
Is it correct to fetch the response from SQL and that $row['count'] should work?
PHP Code:
foreach($status_list as $s){
$sql='SELECT COUNT(tbl_material.status) AS count '.
'FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) '.
'ON tbl_project.project_id = tbl_module.project_id '.
'WHERE project="'.$project.'" AND tbl_material.status="'.$s.'"';
echo $sql.'<br />';
$result= mysql_query($sql, $db_server);
if (!$result) {
echo '10DB Error, could not query the database MySQL Error: '.mysql_error();
exit;
}
$row = mysql_fetch_assoc($result);
$status_results[$i++]=$row['count'];
echo $row['count'].'<br />';
|
|
|
|
|
06-28-2010, 02:15 PM
|
Re: return count from SQL
|
Posts: 2,918
Name: Keith Marshall
Location: Connecticut
|
Try using SELECT COUNT(*) AS count
__________________
<mgraphic /> - I don't have a solution but I admire the problem.
|
|
|
|
|
06-28-2010, 09:05 PM
|
Re: return count from SQL
|
Posts: 173
|
I tried as you suggest but if I understand sql correctly, then I am really just moving a lot more date by using * rather then specifying the actual column.
If I echo the sql statement from the original code and put that into mysql, I get the correct result.
The problem appears to be with the actual return of the result and fetching the record from the query which in this case is just a single row.
ie of data
Name
Dave
John
Dave
Steve
Count the number of "Dave"
Result
Count
2
|
|
|
|
|
06-28-2010, 09:25 PM
|
Re: return count from SQL
|
Posts: 2,918
Name: Keith Marshall
Location: Connecticut
|
When you call SELECT COUNT(*) AS count then you are only retrieving the count total, not loading the data into memory.
__________________
<mgraphic /> - I don't have a solution but I admire the problem.
|
|
|
|
|
06-29-2010, 09:42 PM
|
Re: return count from SQL
|
Posts: 173
|
Okay, so I did as you suggested but the result is still echoing 0 rather then the correct value for the data set.
I don't see the problem, did I fetch the row correctly?
|
|
|
|
|
06-30-2010, 10:03 AM
|
Re: return count from SQL
|
Posts: 2,918
Name: Keith Marshall
Location: Connecticut
|
The result must be empty. You can test it in phpMyAdmin
__________________
<mgraphic /> - I don't have a solution but I admire the problem.
|
|
|
|
|
06-30-2010, 10:59 PM
|
Re: return count from SQL
|
Posts: 173
|
I did test the results in phpMyAdmin. The SQL statement works properly I just don't get the result correctly.
|
|
|
|
|
07-01-2010, 06:44 AM
|
Re: return count from SQL
|
Posts: 3,621
Name: Thierry
Location: I'm the uber Spaminator !
|
My guess would be that maybe as "count" is a reserved word, you cannot use it directly like this.
Try to rename it to cnt, for example, and see what would happen.
Maybe try to do a var_dump($row), just by curiosity.
__________________
Only a biker knows why a dog sticks his head out the window.
|
|
|
|
|
07-01-2010, 07:29 AM
|
Re: return count from SQL
|
Posts: 173
|
That was a good try but still do not have a winner.
|
|
|
|
|
07-01-2010, 08:19 AM
|
Re: return count from SQL
|
Posts: 3,621
Name: Thierry
Location: I'm the uber Spaminator !
|
Did the var_dump() showed the count value ?
__________________
Only a biker knows why a dog sticks his head out the window.
|
|
|
|
|
07-01-2010, 09:00 AM
|
Re: return count from SQL
|
Posts: 173
|
That was a handy function, will have to remember that one.
When I dump, the $status_results array, all I got was a bunch of zeroes.
I am at a loss, the query statement works when I place it into phpMyAdmin. So at this point it would seem that I am not getting a result back from the db for some reason but I am not getting any warnings that the query failed.
When I dump the query result this is what was displayed
Quote:
SELECT COUNT(*) AS cnt FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) ON tbl_project.project_id = tbl_module.project_id WHERE project="3426915 " AND tbl_material.status="Plan.order"
resource(34) of type (mysql result) SELECT COUNT(*) AS cnt FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) ON tbl_project.project_id = tbl_module.project_id WHERE project="3426915 " AND tbl_material.status="PurRequist"
resource(35) of type (mysql result) SELECT COUNT(*) AS cnt FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) ON tbl_project.project_id = tbl_module.project_id WHERE project="3426915 " AND tbl_material.status="POSched.ln"
resource(36) of type (mysql result) SELECT COUNT(*) AS cnt FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) ON tbl_project.project_id = tbl_module.project_id WHERE project="3426915 " AND tbl_material.status="QM InspLot"
resource(37) of type (mysql result) SELECT COUNT(*) AS cnt FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) ON tbl_project.project_id = tbl_module.project_id WHERE project="3426915 " AND tbl_material.status="Plant"
resource(38) of type (mysql result)
|
Here is an update of the latest code:
PHP Code:
//Updates status counts for project
function status_updates(&$project,&$my_material) {
@include 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: ".mysql_error());
mysql_select_db($db_database, $db_server) or die("Unable to select database: ".mysql_error());
$status_list=array("Plan.order","PurRequist","POSched.ln","QM InspLot","Plant");
$i=0;
foreach($status_list as $s){
$sql='SELECT COUNT(*) AS cnt '.
'FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) '.
'ON tbl_project.project_id = tbl_module.project_id '.
'WHERE project="'.$project.'" AND tbl_material.status="'.$s.'"';
echo $sql.'<br />';
$result= mysql_query($sql, $db_server);
if (!$result) {
echo '10DB Error, could not query the database MySQL Error: '.mysql_error();
exit;
}
$row = mysql_fetch_assoc($result);
$status_results[$i++]=$row['cnt'];
var_dump($result);
$row['cnt'].'<br />';
}
mysql_close($db_server);
}
Last edited by dgkindy; 07-01-2010 at 09:05 AM..
Reason: Prematurely hit submit
|
|
|
|
|
07-01-2010, 09:34 AM
|
Re: return count from SQL
|
Posts: 3,621
Name: Thierry
Location: I'm the uber Spaminator !
|
I was more thinking about
PHP Code:
$row = mysql_fetch_assoc($result);
var_dump($row);
To see what PHP gets back from the DB
__________________
Only a biker knows why a dog sticks his head out the window.
|
|
|
|
|
07-01-2010, 05:21 PM
|
Re: return count from SQL
|
Posts: 173
|
Here is the results. So when I look at it, it would appear that the query statement is not working. I have attached a screen capture showing the query working so not sure why it is not working.
Quote:
SELECT COUNT(*) AS cnt FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) ON tbl_project.project_id = tbl_module.project_id WHERE project="3426915 " AND tbl_material.status="Plan.order"
array(1) { ["cnt"]=> string(1) "0" } SELECT COUNT(*) AS cnt FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) ON tbl_project.project_id = tbl_module.project_id WHERE project="3426915 " AND tbl_material.status="PurRequist"
array(1) { ["cnt"]=> string(1) "0" } SELECT COUNT(*) AS cnt FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) ON tbl_project.project_id = tbl_module.project_id WHERE project="3426915 " AND tbl_material.status="POSched.ln"
array(1) { ["cnt"]=> string(1) "0" } SELECT COUNT(*) AS cnt FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) ON tbl_project.project_id = tbl_module.project_id WHERE project="3426915 " AND tbl_material.status="QM InspLot"
array(1) { ["cnt"]=> string(1) "0" } SELECT COUNT(*) AS cnt FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) ON tbl_project.project_id = tbl_module.project_id WHERE project="3426915 " AND tbl_material.status="Plant"
array(1) { ["cnt"]=> string(1) "0" }
|
|
|
|
|
|
07-01-2010, 06:59 PM
|
Re: return count from SQL
|
Posts: 3,621
Name: Thierry
Location: I'm the uber Spaminator !
|
Are you sure you are connected to the same database ?
Because there is virtually no way that the same query works with a result in phpMyAdmin and not via your script if you are accessing the same database...
__________________
Only a biker knows why a dog sticks his head out the window.
|
|
|
|
|
07-01-2010, 08:12 PM
|
Re: return count from SQL
|
Posts: 173
|
I have included another section of the code that is working and I am using the same query and fetch text. I just don't see a difference.
PHP Code:
//Updates status counts for project
function status_updates(&$project,&$my_material) {
@include 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: ".mysql_error());
mysql_select_db($db_database, $db_server) or die("Unable to select database: ".mysql_error());
$status_list=array("Plan.order","PurRequist","POSched.ln","QM InspLot","Plant");
$i=0;
foreach($status_list as $s){
$sql='SELECT COUNT(*) AS cnt '.
'FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) '.
'ON tbl_project.project_id = tbl_module.project_id '.
'WHERE project="'.$project.'" AND tbl_material.status="'.$s.'"';
echo $sql.'<br />';
$result= mysql_query($sql, $db_server);
if (!$result) {
echo '10DB Error, could not query the database MySQL Error: '.mysql_error();
exit;
}
$row = mysql_fetch_assoc($result);
$status_results[$i++]=$row['cnt'];
var_dump($row);
$row['cnt'].'<br />';
}
mysql_close($db_server);
}
PHP Code:
//Dumps data to database
function dump_data(&$project,&$my_material) {
@include 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: ".mysql_error());
mysql_select_db($db_database, $db_server) or die("Unable to select database: ".mysql_error());
//Determine if project already exists in database
$sql = 'SELECT project_id, project FROM tbl_project WHERE project='.$project;
$result= mysql_query($sql, $db_server);
if (!$result) {
echo '1DB Error, could not query the database MySQL Error: '.mysql_error();
exit;
}
//If project exists, return id
if (mysql_num_rows($result)){
$row = mysql_fetch_assoc($result);
$project_id = $row['project_id'];
}
//If project does not exist, add record and return id
else{
$sql='INSERT INTO tbl_project(project) VALUES ('.$project.')';
$result= mysql_query($sql, $db_server);
if (!$result) {
echo '2DB Error, could not query the database MySQL Error: '.mysql_error();
exit;
}
$project_id = mysql_insert_id();
}
//Saves MD4C data to table either module or material tables
foreach ($my_material as &$value) {
$value->part_desc=mysql_real_escape_string($value->part_desc);
//Determine if part is a module or a part
if($value->level==1){
$sql = 'SELECT * FROM tbl_module WHERE module="'.$value->part.'" AND project_id='.$project_id;
$result= mysql_query($sql, $db_server);
if (!$result) {
echo '3DB Error, could not query the database MySQL Error: '.mysql_error();
exit;
}
//Confirms if module exist and returns id
if (mysql_num_rows($result)){
$row = mysql_fetch_assoc($result);
$module_id = $row['module_id'];
$sql_begin='UPDATE tbl_module SET ';
$flag=0;
if(strcmp($value->status,$row['status'])){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='status="'.$value->status.'", flag_status=NOW()';
}
if(strcmp($value->rdate,$row['rdate'])){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='rdate="'.$value->rdate.'", flag_rdate=NOW()';
}
if(!($value->qty=$row['qty'])){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='qty='.$value->qty.', flag_qty=NOW()';
}
if(strcmp($value->prod,$row['prod'])){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='prod="'.$value->prod.'", flag_prod=NOW()';
}
if(strcmp($value->factory,$row['factory'])){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='factory="'.$value->factory.'", flag_factory=NOW()';
}
if($flag){
$sql=$sql_begin.' WHERE module_id='.$module_id;
$result1= mysql_query($sql, $db_server);
if (!$result1) {
echo 'DB Error, Module update not effective. MySQL Error: '.mysql_error();
exit;
}
}
}
//else adds module to table and returns id
else{
$sql_begin='INSERT INTO tbl_module (project_id, module, module_desc, status,rdate, qty, factory';
$sql_end='VALUES ( '.$project_id.', "'.
$value->part.'", "'.
$value->part_desc.'", "'.
$value->status.'", "'.
$value->rdate.'", '.
$value->qty.', "'.
$value->factory.'"';
if(!empty($value->prod)){
$sql_begin.=', prod';
$sql_end.=', "'.($value->prod).'"';
}
$sql = $sql_begin.') '.$sql_end.')';
if (!mysql_query($sql, $db_server)) {
echo '4INSERT failed:'. $sql .'*** '. ($value->line).'<br />'.mysql_error().'<br /><br />';
}
$module_id = mysql_insert_id();
}
}
//records part details to table
else{
$sql = 'SELECT * FROM tbl_material WHERE module_id='.$module_id.' AND part="'.$value->part.'" AND factory="'.$value->factory.'"';
$result= mysql_query($sql, $db_server);
if (!$result) {
echo '6DB Error, could not query the database MySQL Error: '.mysql_error();
exit;
}
//Material exists in table, update details as required
if (mysql_num_rows($result)){
$row = mysql_fetch_assoc($result);
$material_id = $row['material_id'];
$sql_begin='UPDATE tbl_material SET ';
$flag=0;
if($value->status<>$row['status']){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='status="'.$value->status.'", flag_status=NOW()';
}
if($value->qty<>$row['qty']){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='qty='.$value->qty.', flag_qty=NOW()';
}
if($value->edate<>$row['edate']){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='edate="'.$value->edate.'", flag_edate=NOW()';
}
if($value->rdate<>$row['rdate']){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='rdate="'.$value->rdate.'", flag_rdate=NOW()';
}
if($value->pr<>$row['pr']){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='pr="'.$value->pr.'", flag_pr=NOW()';
}
if($value->po<>$row['po']){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='po="'.$value->po.'", flag_po=NOW()';
}
if($value->line<>$row['line']){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='line="'.$value->line.'", flag_line=NOW()';
}
if($value->rdate<>$row['factory']){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='factory="'.$value->factory.'", flag_factory=NOW()';
}
if($value->rdate<>$row['error']){
if($flag) $sql_begin.=', ';
$flag=1;
$sql_begin.='error="'.$value->error.'", flag_error=NOW()';
}
if($flag){
$sql=$sql_begin.' WHERE material_id='.$material_id;
$result1= mysql_query($sql, $db_server);
if (!$result1) {
echo '7DB Error7, Material update not effective. MySQL Error: '.mysql_error();
exit;
}
}
}
//Material does not exist, add recordset to table
else{
$sql_begin='INSERT INTO tbl_material (module_id, level, part, part_desc';
$sql_end= 'VALUES ('.$module_id.', "'.
$value->level.'", "'.
$value->part.'" ,"'.
$value->part_desc.'" ';
if(!empty($value->status)){
$sql_begin.=', status, flag_status';
$sql_end.=', "'.($value->status).'" , Now()';
}
if(!empty($value->edate)){
$sql_begin.=', edate, flag_edate';
$sql_end.=', "'.($value->edate).'" , Now()';
}
if(!empty($value->rdate)){
$sql_begin.=', rdate, flag_rdate';
$sql_end.=', "'.($value->rdate).'" , Now()';
}
if(!empty($value->qty)){
$sql_begin.=', qty, flag_qty';
$sql_end.=', '.($value->qty).' , Now()';
}
if(!empty($value->pr)){
$sql_begin.=', pr, flag_pr';
$sql_end.=', "'.($value->pr).'" , Now()';
}
if(!empty($value->po)){
$sql_begin.=', po, flag_po';
$sql_end.=', "'.($value->po).'" , Now()';
}
if(!empty($value->line)){
$sql_begin.=', line, flag_line';
$sql_end.=', "'.($value->line).'" , Now()';
}
if(!empty($value->factory)){
$sql_begin.=', factory, flag_factory';
$sql_end.=', "'.($value->factory).'" , Now()';
}
if(!empty($value->error)){
$sql_begin.=', error, flag_error';
$sql_end.=', "'.($value->error).'" , Now()';
}
$query = $sql_begin.') '.$sql_end.')';
if (!mysql_query($query, $db_server)) echo '5INSERT failed:'. $query .'*** '. ($value->line).'<br />'.mysql_error().'<br /><br />';
}
}
}
// close connection to database server
mysql_close($db_server);
}
|
|
|
|
|
07-02-2010, 02:51 AM
|
Re: return count from SQL
|
Posts: 3,621
Name: Thierry
Location: I'm the uber Spaminator !
|
if you print $db_hostname and $db_database, does those point to the correct server?
__________________
Only a biker knows why a dog sticks his head out the window.
|
|
|
|
|
07-02-2010, 01:02 PM
|
Re: return count from SQL
|
Posts: 173
|
I added the print statement to two locations. One funtion that I know is working and the one that is not. Both print out the same details.
Quote:
|
string(9) "localhost" string(3) "php" string(9) "localhost" string(3) "php" string(9) "localhost" string(3) "php" string(9) "localhost" string(3) "php" string(9) "localhost" string(3) "php" string(9) "localhost" string(3) "php"
|
|
|
|
|
|
|
« Reply to return count from SQL
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
