• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

MySQL Problem

The following query has issues when run from php.

SELECT * from workorder where cid = 18391 and wokey <> 193876 and dateentered > '2004-01-22' order by dateentered desc limit 50

If I use phpmyadmin & execute the query directly, it works fine.

When I run it in php (as follows), it gets into an infinite loop in the browser & eventually FireFox crashes.

$qryw = "SELECT * from workorder where cid = " . $c['cid'] . " and wokey <> " . $woid . " and dateentered > '" . $date10 . "' order by dateentered desc limit 50";

echo "qry = " . $qryw . "<br>";
$resw = mysql_query ($qryw, $Link);
$nw = mysql_fetch_array($resw);

To confirm that the php is right, I copied the query from the echo to run it in phpmysql.

Table structure attached.

There are over 91K rows in the table.

What is the issue?
wo-table-str.jpg
0
Richard Korts
Asked:
Richard Korts
  • 3
  • 2
1 Solution
 
MurfurFull Stack DeveloperCommented:
It may be because you have not added single quotes to the variables $c['cid'] & $woid which
phpMyAdmin will add automatically.

$qryw = "SELECT * from workorder where cid = '" . $c['cid'] . "' and wokey <> '" . $woid . "' and dateentered > '" . $date10 . "' order by dateentered desc limit 50";

Open in new window

0
 
Richard KortsAuthor Commented:
cid & wokey (as well as the php variables) are all integer, The ' is not needed; in fact, it will cause it to malfunction because the data types are integer.

Can it be because there is no key (index) field defined on the workorder table?

Thanks
0
 
Chris StanyonCommented:
Not having an index won't crash the system (although it may slow it down)

Post the whole PHP code - you say it get's into an infinite loop, but nothing in your code shows a loop. mysql_fetch_array() simply grabs the next record from the recordset, so you normally use that as part of the loop:

$resw = mysql_query($qryw);
while ($nw = mysql_fetch_array($resw)) {
   var_dump($nw)
}

Open in new window

FYI - PHP is dropping the mysql_* library so you should be switching to mySQLi or PDO
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Richard KortsAuthor Commented:
I've attached the program; I have changed it slightly in trying a slightly different approach; no change, it still crashes Firefox.

Of course you can't run it because you can't connect to the server & I prefer NOT to put the db name & password into an open forum.
show-prior-wos.php
0
 
Chris StanyonCommented:
Hmm - it's all a bit of a mess really :(

Without debugging your code in full there's a couple of things that will fail. You have these lines:

$qryw = "SELECT * from workorder where (cid = " . $c['cid'] . " and wokey <> " . $woid . " and datecompl < '" . $today . "' ) order by datecompl desc";
//echo "qry = " . $qryw . "<br>";
$resw = mysql_query ($qryw, $Link);
$nw = mysql_fetch_array($resw);
echo "num selected = " . $nw . "<br>";

Open in new window

mysql_fetch_array() pulls a record from the database into a variable ($nw), but you seem to think it somehow gives you a record count!!

You then try and use the record as a row counter in the for loop!!

for ($i = 0; $i < $nw; $i++) {

Open in new window

That will fail miserably.

First off, turn on error reporting at the start of your script - it will give you specific info about what's failing. Add this right at the start of your script:

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);
?>

Open in new window

What you should do is something like this:

//To get the row count
$qryw = "your SQL statement";
$resw = mysql_query($qryw);
echo "num selected = " . mysql_num_rows($resw) . "<br>";

//To loop through the records:
<?php while ($w = mysql_fetch_array($resw)): ?>
<tr>...</tr>
<?php endwhile; ?>

Open in new window

0
 
Richard KortsAuthor Commented:
Thanks, obviously I did not intend that logic, $nw was intended to be the number of rows in the result set, just a mistake.

Thanks for seeing it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now