Solved

MySQL Problem

Posted on 2014-01-22
6
385 Views
Last Modified: 2014-01-22
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
Comment
Question by:Richard Korts
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:Murfur
ID: 39801544
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
 

Author Comment

by:Richard Korts
ID: 39801570
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
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39801604
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Richard Korts
ID: 39801635
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
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39801711
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
 

Author Closing Comment

by:Richard Korts
ID: 39801771
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now