Solved

MySQL Problem

Posted on 2014-01-22
6
387 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 43

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 43

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel - SQL export question 3 42
Fulfillment API php code sample 1 41
PHP AJAX JSON 2 61
Eloquent ORM manual paginator defaults to simple 2 24
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

777 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