QUERY Hell

[DSTR3]

I have three queries that I am trying to run. The first one builds the table tblTemp based on the criteria selected from a multi-select dropdown. This works fine. (Alone) The second one builds the record set based on the count of the options selected in the dropdown. The third one deletes the records from the tblTemp when all is done. For some reason they are not working together. I think its the IF statement thats fouling things up. Have a look.

 

include "config.php";
 $mysqlQuery = "INSERT into tblTemp (LocationID,DetailID,DetailName)
 SELECT tblLocDet.LocationID, tblLocDet.DetailID, tblDetails.DetailName
 FROM tblLocDet
 INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
 WHERE tblDetails.DetailName IN (" . $criteria . ");";

 $mysqlQuery = ("SELECT tblTemp.LocationID, Count(tblTemp.LocationID) AS CountOfLocationID
 FROM tblTemp
 GROUP BY tblTemp.LocationID
 HAVING Count(tblTemp.LocationID)< $nchecked");

 if (! $rs = mysql_query ( $mysqlQuery )) {
  echo "Cannot parse query";
  } elseif (mysql_num_rows ( $rs ) == 0) {
   echo "No records found";
 } else {
  echo "<table id=\"myTable\" table width=\"710\" class=\"beautifuldata\" align=\"Left\"
  cellspacing=\"0\">\n";
  echo "<thead>\n<tr>";
  echo "<th>PLACE</th>";
  echo "<th>ADDRESS</th>";
  echo "<th>PHONE</th>";
  echo "<th>PRICE</th>";
  echo "<th>RATING</th>";
  echo "</tr>\n</thead>\n";
  while ( $row = mysql_fetch_array ( $rs ) ) {
  echo "<tr><td><strong><a href='" . $row [RestPage] . "'>" . $row ['RestName'] . "</a>
  </strong></td>";
  echo "<td>" . $row ['LocationID'] . "</td>";
  echo "<td>" . $row ['Phone'] . "</td>";
  echo "<td>" . $row ['Price'] . "</td>";
  echo "<td>" . $row ['Rating'] . "</td>";
  echo "</tr>";
  }
  }
  echo "</table><br />\n";
  $mysqlQuery = "DELETE FROM tblTemp";
  mysql_close ( $con );
  }

[Barand]

  Quote

HAVING Count(tblTemp.LocationID)< $nchecked

I'd have thought you would want

 

HAVING Count(tblTemp.LocationID) >= $nchecked

 

That aside, what does "not working" mean - it tells us nothing.

[DSTR3]

Your right! First its suppose to insert records into tblTemp. Its not. Next its suppose to Select records and return the results. its not. Third, its suppose to delete the records from the tblTemp. Its not. Pretty well broke I'd say. I would appreciate your help in this. From what I gather you are VERY knowledgable. Thank you.

 

PS you were right about this...HAVING Count(tblTemp.LocationID) >= $nchecked

[DSTR3]

The first one that inserts the records to tblTemp only works if I REM out the second query. The SELECT query.

[DSTR3]

Moving things around a bit, I have the first one working, However; the second query has yet to return any results. I made it == cause it has to be equal. not less than or greater than, but equal. Also receiving an ERROR on the line """ elseif (mysql_num_rows ( $rs ) == 0) {""" saying it's not valid.

 

include "config.php"; 
 $mysqlQuery = "INSERT into tblTemp (LocationID,DetailID,DetailName)
 SELECT tblLocDet.LocationID, tblLocDet.DetailID, tblDetails.DetailName
 FROM tblLocDet
 INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
 WHERE tblDetails.DetailName IN (" . $criteria . ");";
 if (! $rs = mysql_query ( $mysqlQuery )) {
  echo "Cannot parse query";
  } elseif (mysql_num_rows ( $rs ) == 0) {
   echo "No records found";
 } else {
 $mysqlQuery = ("SELECT tblTemp.LocationID, Count(tblTemp.LocationID) AS CountOfLocationID
 FROM tblTemp
 GROUP BY tblTemp.LocationID
 HAVING Count(tblTemp.LocationID) == '$nchecked'");
  echo "<table id=\"myTable\" table width=\"710\" class=\"beautifuldata\" align=\"Left\"
  cellspacing=\"0\">\n";
  echo "<thead>\n<tr>";
  echo "<th>PLACE</th>";
  echo "<th>ADDRESS</th>";
  echo "<th>PHONE</th>";
  echo "<th>PRICE</th>";
  echo "<th>RATING</th>";
  echo "</tr>\n</thead>\n";
  while ( $row = mysql_fetch_array ( $rs ) ) {
  echo "<tr><td><strong><a href='" . $row [RestPage] . "'>" . $row ['RestName'] . "</a>
  </strong></td>";
  echo "<td>" . $row ['LocationID'] . "</td>";
  echo "<td>" . $row ['Phone'] . "</td>";
  echo "<td>" . $row ['Price'] . "</td>";
  echo "<td>" . $row ['Rating'] . "</td>";
  echo "</tr>";
  }
  }
  echo "</table><br />\n";
  //$mysqlQuery = "DELETE FROM tblTemp";
  mysql_close ();
  }
 ?>

[Barand]

Your 2nd query selects an id and a count yet you are expecting the rows to contain phone, price, rating etc

[kicken]

  On 2/11/2013 at 2:06 AM, DSTR3 said:

I made it == cause it has to be equal. not less than or greater than, but equal.

 

For SQL you only use a single = in a comparison:

Count(tblTemp.LocationID) = '$nchecked'

 

 

  Quote

Also receiving an ERROR on the line """ elseif (mysql_num_rows ( $rs ) == 0) {""" saying it's not valid.

What's the exact error message?

 

 

[DSTR3]

Barand Don't worry about that. I'll adjust the query later. I had it coughing out LocationID earlier. Don't know why it stopped. It's either the position of the queries, the naming of the queries or the variable. If ypu want I can change those output fields. kicken the complete message.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/d/s/t/dstr3/html/MENUHEAD/Steelers/Czeh.php on line 96

 

 

 

 

[DavidAM]

  On 2/11/2013 at 12:37 AM, DSTR3 said:

include "config.php";
 $mysqlQuery = "INSERT into tblTemp (LocationID,DetailID,DetailName)
    SELECT tblLocDet.LocationID, tblLocDet.DetailID, tblDetails.DetailName
    FROM tblLocDet
    INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
    WHERE tblDetails.DetailName IN (" . $criteria . ");";

 $mysqlQuery = ("SELECT tblTemp.LocationID, Count(tblTemp.LocationID) AS CountOfLocationID
    FROM tblTemp
    GROUP BY tblTemp.LocationID
    HAVING Count(tblTemp.LocationID)< $nchecked");

 if (! $rs = mysql_query ( $mysqlQuery )) {

 

In your original post, you are assigning the INSERT SQL string to a variable, and then you immediately overwrite that variable with the SELECT SQL string. YOU NEVER EXECUTED THE INSERT. So there is nothing to SELECT when you DO execute the SELECT statement.

[DSTR3]

So that is why it is working when I move it down! So it must be the variable $nchecked. But also I think its the mysql_numrows perhaps. At this point now.

<?php
 //This is for the button///////////////////////////////////
 if (isset ( $_POST ["btnSearch"] )) {
 echo "<br>Selected Options are :<br>";
 $checked = $_POST ["criteria"];

 $criteria = "";
 $separator = ", ";
 for($i = 0; $i < count ( $checked ); $i ++) {
 echo " " . $checked [$i] . "<br/>";

 if ($i == count ( $checked ) - 1) {
  $separator = "";
 }
 $criteria = $criteria . "'" . $checked [$i] . "'" . $separator;
 }
 //This gives a count of the number of options that have been selected////////////////////////
 $nchecked = count($checked);
 echo("<p>$nchecked filter(s) HEROselected:<br>");
 for($i=0; $i < $nchecked; $i++)
 {
 echo($checked[$i] . "<br/>");
 }
 echo("</p>");
 //END COUNT////////////////////////////////////
 echo "<br><br>";
 echo $criteria . "<br><br>";
 include "config.php"; 
 $mysqlQuery = "INSERT into tblTemp (LocationID,DetailID,DetailName)
 SELECT tblLocDet.LocationID, tblLocDet.DetailID, tblDetails.DetailName
 FROM tblLocDet
 INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
 WHERE tblDetails.DetailName IN (" . $criteria . ");";


 if (! $rs = mysql_query ( $mysqlQuery )) {
  echo "Cannot parse query";
  } elseif (mysql_num_rows ( $rs ) == 0) {
   echo "No records found";
 } else {
 $mysqlQuery = ("SELECT tblTemp.LocationID, Count(tblTemp.LocationID) AS CountOfLocationID
 FROM tblTemp
 GROUP BY tblTemp.LocationID
 HAVING Count(tblTemp.LocationID) == '$nchecked'");
  echo "<table id=\"myTable\" table width=\"710\" class=\"beautifuldata\" align=\"Left\"
  cellspacing=\"0\">\n";
  echo "<thead>\n<tr>";
  echo "<th>LocationID</th>";
  echo "</tr>\n</thead>\n";
  while ( $row = mysql_fetch_array ( $rs ) ) {
  echo "<td>" . $row ['LocationID'] . "</td>";
  echo "</tr>";
  }
  }
  echo "</table><br />\n";
  //$mysqlQuery3 = "DELETE FROM tblTemp";
  mysql_close ();
  }
 ?>
 </p>
</body>
</html>

[DavidAM]

  On 2/11/2013 at 2:42 AM, DSTR3 said:

    } else {
    $mysqlQuery = ("SELECT tblTemp.LocationID, Count(tblTemp.LocationID) AS CountOfLocationID
    FROM tblTemp
    GROUP BY tblTemp.LocationID
    HAVING Count(tblTemp.LocationID) == '$nchecked'");
     echo "<table id=\"myTable\" table width=\"710\" class=\"beautifuldata\" align=\"Left\"
     cellspacing=\"0\">\n";
     echo "<thead>\n<tr>";
     echo "<th>LocationID</th>";
     echo "</tr>\n</thead>\n";
     while ( $row = mysql_fetch_array ( $rs ) ) {
     echo "<td>" . $row ['LocationID'] . "</td>";
     echo "</tr>";
     }
     }
     echo "</table><br />\n";
  //$mysqlQuery3 = "DELETE FROM tblTemp";
  mysql_close ();
  }

 

Again, you are not executing the query (the SELECT query this time). You need a mysql_query($mysqlQuery) in there.

 

And by the way, you are not executing the DELETE query either.

[DSTR3]

OK, Being new could you please show me where to place mysql_query($mysqlQuery) thank you.

[DSTR3]

I tried a constant in the second query and it still did not select the records. The insert and the delete query are working fine.

[DSTR3]

Tried to place this earlier. Couldn't for some reason.

 

<?php
//This is for the button///////////////////////////////////
if (isset ( $_POST ["btnSearch"] )) {
echo "<br>Selected Options are :<br>";
$checked = $_POST ["criteria"];

$criteria = "";
$separator = ", ";
for($i = 0; $i < count ( $checked ); $i ++) {
echo " " . $checked [$i] . "<br/>";

if ($i == count ( $checked ) - 1) {
$separator = "";
}
$criteria = $criteria . "'" . $checked [$i] . "'" . $separator;
}
//This gives a count of the number of options that have been selected////////////////////////
$nchecked = count($checked);
echo("<p>$nchecked filter(s) HEROselected:<br>");
for($i=0; $i < $nchecked; $i++)
{
echo($checked[$i] . "<br/>");
}
echo("</p>");
//END COUNT////////////////////////////////////
echo "<br><br>";
echo $criteria . "<br><br>";
include "config.php";
$mysqlQuery = "INSERT into tblTemp (LocationID,DetailID,DetailName)
 SELECT tblLocDet.LocationID, tblLocDet.DetailID, tblDetails.DetailName
 FROM tblLocDet
 INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
 WHERE tblDetails.DetailName IN (" . $criteria . ");";


if (! $rs = mysql_query ( $mysqlQuery )) {
echo "Cannot parse query";
} elseif (mysql_num_rows ( $rs ) == 0) {
echo "No records found";
 } else {
 $mysqlQuery2 = ("SELECT tblTemp.LocationID, Count(tblTemp.LocationID) AS CountOfLocationID
 FROM tblTemp
 GROUP BY tblTemp.LocationID
 HAVING Count(tblTemp.LocationID) == 2");
 mysql_query($mysqlQuery2);
 echo "<table id=\"myTable\" table width=\"710\" class=\"beautifuldata\" align=\"Left\"
 cellspacing=\"0\">\n";
 echo "<thead>\n<tr>";
 echo "<th>LocationID</th>";
 echo "</tr>\n</thead>\n";
 while ( $row = mysql_fetch_array ( $rs ) ) {
 echo "<td>" . $row ['LocationID'] . "</td>";
 echo "</tr>";
 }
 }
 echo "</table><br />\n";
//$mysqlQuery3 = "DELETE FROM tblTemp";
//mysql_query($mysqlQuery3);
mysql_close ();
}
?>
</p>

 

Still getting this.......

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/d/s/t/dstr3/html/MENUHEAD/Steelers/Czeh.php on line 98

No records found

[DavidAM]

Anytime you get that message, it means the query failed. You have to echo mysql_error() just after the mysql_query() call to see what the SQL error is.

 

Also, you are not assigning the result of the second query (the SELECT) to a variable, so your call to fetch_array is going to fail as well. You need to use $rs = mysql_query($mysqlQuery2); up there when you execute that SELECT.

[DSTR3]

OK I did it.....still no go. Message now is cannot parse query. The warning is gone. I suppose its down to that variable &nchecked.?

 

if (! $rs = mysql_query ( $mysqlQuery2 )) {
echo "Cannot parse query";
} elseif (mysql_num_rows ( $rs ) == 0) {
echo "No records found";
 } else {
 $mysqlQuery2 = ("SELECT tblTemp.LocationID, Count(tblTemp.LocationID) AS CountOfLocationID
 FROM tblTemp
 GROUP BY tblTemp.LocationID
 HAVING Count(tblTemp.LocationID) == '$nchecked'");
 mysql_query($mysqlQuery2);
   echo mysql_error($mysqlQuery2);

[DSTR3]

OK this is all setup but still getting the error message cannot parse query. I run the query2 in Navicat and the recorda come up fine. The only thing that I am changing is the variable '$nchecked' I even tried a constant, the same that I am using in Navicat and I get the Error 'Cannot Parse Query".....

 

include "config.php"; 
 $mysqlQuery = "INSERT into tblTemp (LocationID,DetailID,DetailName)
 SELECT tblLocDet.LocationID, tblLocDet.DetailID, tblDetails.DetailName
 FROM tblLocDet
 INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
 WHERE tblDetails.DetailName IN (" . $criteria . ");";
 mysql_query($mysqlQuery);

 if (! $rs = mysql_query ( $mysqlQuery2 )) {
  echo "Cannot parse query";
  } elseif (mysql_num_rows ( $rs ) == 0) {
   echo "No records found";
 } else {
 $mysqlQuery2 = ("SELECT tblTemp.LocationID, Count(tblTemp.LocationID) AS CountOfLocationID
 FROM tblTemp
 GROUP BY tblTemp.LocationID
 HAVING Count(tblTemp.LocationID) == '$nchecked'");
 mysql_query($mysqlQuery2);
 die (mysql_error());
  echo "<table id=\"myTable\" table width=\"710\" class=\"beautifuldata\" align=\"Left\"
  cellspacing=\"0\">\n";
  echo "<thead>\n<tr>";
  echo "<th>LocationID</th>";
  echo "</tr>\n</thead>\n";
  while ( $row = mysql_fetch_array ( $rs ) ) {
  echo "<td>" . $row ['LocationID'] . "</td>";
  echo "</tr>";
  }
  }
  echo "</table><br />\n";
  //$mysqlQuery3 = "DELETE FROM tblTemp";
  //mysql_query($mysqlQuery3);
  mysql_close ();
  }
 ?>

[DSTR3]

I'm done. This works! Thank you everyone. Especially you DavidAM.

 

include "config.php"; 
 $mysqlQuery = "INSERT into tblTemp (LocationID,DetailID,DetailName)
 SELECT tblLocDet.LocationID, tblLocDet.DetailID, tblDetails.DetailName
 FROM tblLocDet
 INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
 WHERE tblDetails.DetailName IN (" . $criteria . ");";
 mysql_query($mysqlQuery);

 $mysqlQuery2 = ("SELECT tblTemp.LocationID, Count(tblTemp.LocationID) AS CountOfLocationID
 FROM tblTemp
 GROUP BY tblTemp.LocationID
 HAVING Count(tblTemp.LocationID) = '$nchecked'");
 mysql_query($mysqlQuery2);  

 if (! $rs = mysql_query ( $mysqlQuery2 )) {
  echo "Cannot parse query";
  } elseif (mysql_num_rows ( $rs ) == 0) {
   echo "No records found";
 } else {
 echo (mysql_error());
  echo "<table id=\"myTable\" table width=\"710\" class=\"beautifuldata\" align=\"Left\"
  cellspacing=\"0\">\n";
  echo "<thead>\n<tr>";
  echo "<th>PLACE</th>";
  echo "<th>ADDRESS</th>";
  echo "<th>PHONE</th>";
  echo "<th>PRICE</th>";
  echo "<th>RATING</th>";
  echo "</tr>\n</thead>\n";
  while ( $row = mysql_fetch_array ( $rs ) ) {
  echo"<tr>
  <td><strong><a href='$row[RestPage]'>$row[RestName]</a></strong></td>
  <td>$row[LocationID]</td>
  <td>$row[Phone]</td>
  <td>$row[Price]</td>
  <td>$row[Rating]</td>
  </tr>\n";
  }
  echo "</table><br />\n";
  }
  $mysqlQuery3 = "DELETE FROM tblTemp";
  mysql_query($mysqlQuery3);
  mysql_close ();
  }
 ?>