Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL Problem

Posted on 2014-01-22
6
Medium Priority
?
392 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 44

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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 44

Accepted Solution

by:
Chris Stanyon earned 2000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

618 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