Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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