Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › PHP Query returning unwanted result
New Posts  All Forums:Forum Nav:

PHP Query returning unwanted result

post #1 of 8
Thread Starter 
I'm writing database driven control panel and want to display a notice at the top of the page if the the right data exists.
Code:
$getnotice = "SELECT penalties.time_expire, penalties.client_id, clients.id FROM penalties, clients WHERE penalties.client_id = clients.id AND penalties.inactive = 0 AND penalties.client_id = $clientid" or die(mysql_error());
$nresult = mysql_query($getnotice);
$nrows = mysql_fetch_array($nresult);

if ($nrows['time_expire'] == -1 || $nrows['time_expire'] > time()) {
    echo mynoticehere;
}

The above query will return all rows that meet that condition. In this instance, the page viewer is viewing a details page for a specific user. The user id in the database and for the page is $clientid. If the client we are looking at has an active penalty, (the time_expire column in the database contains -1 or the stored timestamp is a further date than right now), a notice is displayed.

Works well, however, if a client has more than one penalty, the if statement may not display the notice because the first row in the array it looks at doesn't meet the conditions. (They may have had another penalty that previously expired, hence not active)

How can I change that?
Edited by Ryanb213 - 7/11/13 at 3:39pm
post #2 of 8
i havent worked much with php mysql functions but if its similar to ruby, it should return an array. try checking the length (number of elements) of $nrows, and then looping through them incrementing
Code:
$nrows[i]['time_expire']

from a quick search it appears if you loop calling mysql_fetch_array() over and over until it returns null you will get the next data set.

something like so
Code:
$i = 0;
$records = mysql_num_rows($sections_query);
while($row_sections = mysql_fetch_array($sections_query))
{
    echo "<h3>" . $row_sections['section_name'] . "</h3>";
}

edit:: your code would look possibly something like this
Code:
$getnotice = "SELECT penalties.time_expire, penalties.client_id, clients.id FROM penalties, clients WHERE penalties.client_id = clients.id AND penalties.inactive = 0 AND penalties.client_id = $clientid" or die(mysql_error());
$nresult = mysql_query($getnotice);

while($nrows = mysql_fetch_array($nresult))
{
   if ($nrows['time_expire'] == -1 || $nrows['time_expire'] > time()) {
       echo mynoticehere;
   }
}

Edited by abduct - 7/11/13 at 3:57pm
post #3 of 8
Thread Starter 
Quote:
Originally Posted by abduct View Post

i havent worked much with php mysql functions but if its similar to ruby, it should return an array. try checking the length (number of elements) of $nrows, and then looping through them incrementing
Code:
$nrows[i]['time_expire']

from a quick search it appears if you loop calling mysql_fetch_array() over and over until it returns null you will get the next data set.

something like so
Code:
$i = 0;
$records = mysql_num_rows($sections_query);
while($row_sections = mysql_fetch_array($sections_query))
{
    echo "<h3>" . $row_sections['section_name'] . "</h3>";
}

edit:: your code would look possibly something like this
Code:
$getnotice = "SELECT penalties.time_expire, penalties.client_id, clients.id FROM penalties, clients WHERE penalties.client_id = clients.id AND penalties.inactive = 0 AND penalties.client_id = $clientid" or die(mysql_error());
$nresult = mysql_query($getnotice);

while($nrows = mysql_fetch_array($nresult))
{
   if ($nrows['time_expire'] == -1 || $nrows['time_expire'] > time()) {
       echo mynoticehere;
   }
}

I had thought of this,

However in the rare event that two of the rows match the criteria the notice will show twice.
post #4 of 8
what is -1 supposed to simulate? a previous penalty that has been endured? if you dont need to work with the variables themself returned from mysql you could always use a boolean flag to print your notice
Code:
$getnotice = "SELECT penalties.time_expire, penalties.client_id, clients.id FROM penalties, clients WHERE penalties.client_id = clients.id AND penalties.inactive = 0 AND penalties.client_id = $clientid" or die(mysql_error());
$nresult = mysql_query($getnotice);
$print_notice = false;
while($nrows = mysql_fetch_array($nresult))
{
   if ($nrows['time_expire'] == -1 || $nrows['time_expire'] > time()) {
       $print_notice = true;
   }
}

if($print_notice) echo $mynotice;

alternatively you can print your notice as soon as your if statement evals to true and then break from the loop also resulting in only one message being displayed, which in my opinion would be a better way to go.

edit:: if you need to work with the returned data you would need to do something like this.
Code:
$getnotice = "SELECT penalties.time_expire, penalties.client_id, clients.id FROM penalties, clients WHERE penalties.client_id = clients.id AND penalties.inactive = 0 AND penalties.client_id = $clientid" or die(mysql_error());
$nresult = mysql_query($getnotice);
$print_msg = '';
while($nrows = mysql_fetch_array($nresult))
{
   if($nrows['time_expire'] == -1) $print_msg = '-1';
   elseif($nrows['time_expire'] > time()) $print_msg = $nrows['time_expire'];
}

echo $print_msg;
then again this depends if you need to work with the results returned from mysql or not.

edit3: depending on database setup you could always assume the last returned result is always the newest penalty and just loop until you get to the last entry and then print that. if this is the way it is setup you could essentially change your sql statement to reflect this so that you only return 1 result via using the ORDER BY clause and ordering the penalties from newest to oldest, in which your original code would work.

edit:: sorry for all the edits >.>
Edited by abduct - 7/11/13 at 4:23pm
post #5 of 8
Thread Starter 
Quote:
Originally Posted by abduct View Post

what is -1 supposed to simulate? a previous penalty that has been endured? if you dont need to work with the variables themself returned from mysql you could always use a boolean flag to print your notice
Code:
$getnotice = "SELECT penalties.time_expire, penalties.client_id, clients.id FROM penalties, clients WHERE penalties.client_id = clients.id AND penalties.inactive = 0 AND penalties.client_id = $clientid" or die(mysql_error());
$nresult = mysql_query($getnotice);
$print_notice = false;
while($nrows = mysql_fetch_array($nresult))
{
   if ($nrows['time_expire'] == -1 || $nrows['time_expire'] > time()) {
       $print_notice = true;
   }
}

if($print_notice) echo $mynotice;

alternatively you can print your notice as soon as your if statement evals to true and then break from the loop also resulting in only one message being displayed, which in my opinion would be a better way to go.

edit:: if you need to work with the returned data you would need to do something like this.
Code:
$getnotice = "SELECT penalties.time_expire, penalties.client_id, clients.id FROM penalties, clients WHERE penalties.client_id = clients.id AND penalties.inactive = 0 AND penalties.client_id = $clientid" or die(mysql_error());
$nresult = mysql_query($getnotice);
$print_msg = '';
while($nrows = mysql_fetch_array($nresult))
{
   if($nrows['time_expire'] == -1) $print_msg = '-1';
   elseif($nrows['time_expire'] > time()) $print_msg = $nrows['time_expire'];
}

echo $print_msg;
then again this depends if you need to work with the results returned from mysql or not.

edit3: depending on database setup you could always assume the last returned result is always the newest penalty and just loop until you get to the last entry and then print that. if this is the way it is setup you could essentially change your sql statement to reflect this so that you only return 1 result via using the ORDER BY clause and ordering the penalties from newest to oldest, in which your original code would work.

edit:: sorry for all the edits >.>

The first piece seems to solve the issue. Dunno why I didn't think of that. Also to answer your question -1 means never. Its a permanent penalty.

Thanks a ton bud. Repped you a few times.
post #6 of 8
A better option still would be to update your query to contain the same filter. Why bother returning lots of results that will never meet your condition and then having to loop through them to find ones that do? You should extend your WHERE clause and then simply check if there are any returned records.
    
CPUMotherboardGraphicsRAM
i7 920 D0 MSI X58 Pro-E GTX 560 Ti 448 3x2GB G.Skill DDR3-1333 9-9-9-24 
Hard DriveHard DriveOptical DriveOS
840 Pro Caviar Black LG BD-ROM Windows 8.1 Pro x64 
MonitorMonitorKeyboardPower
Dell U2713HM Dell U2311H Turbo-Trak (Google it :D) Corsair HX-520 
CaseMouseMouse PadAudio
CM690 Mionix Avior 7000 Everglide Titan AKG K 242 HD 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
i7 920 D0 MSI X58 Pro-E GTX 560 Ti 448 3x2GB G.Skill DDR3-1333 9-9-9-24 
Hard DriveHard DriveOptical DriveOS
840 Pro Caviar Black LG BD-ROM Windows 8.1 Pro x64 
MonitorMonitorKeyboardPower
Dell U2713HM Dell U2311H Turbo-Trak (Google it :D) Corsair HX-520 
CaseMouseMouse PadAudio
CM690 Mionix Avior 7000 Everglide Titan AKG K 242 HD 
  hide details  
Reply
post #7 of 8
Is this publicly accessible? Because if so, you're SQL request has left itself open to SQL-injection attacks. You really should be using parameterized queries in mysqli: http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php

In fact, I'd recommend rewriting you PHP SQL query code to use parameterized prepare statements in mysqli regardless of whether your code is running internally or on the web.
post #8 of 8
Quote:
Originally Posted by Plan9 View Post

Is this publicly accessible? Because if so, you're SQL request has left itself open to SQL-injection attacks. You really should be using parameterized queries in mysqli: http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php

In fact, I'd recommend rewriting you PHP SQL query code to use parameterized prepare statements in mysqli regardless of whether your code is running internally or on the web.

this^

i thought about mentioning it, but he didnt post how he was obtaining his variable and if he was sanitizing it or not before use so i left it out.
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › PHP Query returning unwanted result