A query returns error and don’t behaves as expected.

@HellgYJan 11.2006

Hi!,
I used the following source:

[code=php] $query=”SELECT ID FROM users WHERE name='”.$_POST[‘name’].” WHERE status=’2′ OR date > ‘”.time()- (60*60*24) .”‘”; $result=mysql_query($query, $link);[/code]

I recall that i read somewhere that when either one of the statements of an OR condition returns TRUE, the whole statement returns true without proceeding to the next conditions…
There fore i used this query to select all the users that are status ‘2’(active accounts), and in case that the account isn’t active(this condition will be examined only under such circumstances), i check whether it was submitted
less then 24 hours ago(the period in which the user can active his account following a link that he received to his mail box).
Anyways, this phrase returns the following error, and i don’t have clue why.

~~[QUOTE]~~

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘-86400” at line 1

[/QUOTE]

First it was the dividing point that was next by the number and was processed as a part of the number, but i got that fixed, what is it now:.
86400 appears to be exactly the multiplication of 60*60*24, it is almost as time(), from some weird reason doesn’t returns value. ~~[B]~~I can’t apply math between the fields in the query themselves, or with external values can i? [/B].
The same function seems to work just fine with other queries.

I also have another question, i read that the function mail() returns TRUE value if it was successful and FALSE if it was not, is it true?.
can i used in the following way to determine whether the user receive the email to his address or not?.

[code=php] if(!mail($_POST[’email’], $row[‘title’], $row[‘message’])) { Error handling } [/code]

to post a comment

PHP

23 Comments(s) _↴

@chazzyJan 11.2006 — #syntax error

<i>
 </i>SELECT ID FROM users WHERE name='".$_POST['name']." AND
 (status='2' OR date &gt; '".time()- (60*60*24) ."');

@HellgYauthorJan 11.2006 — #

[code=php]	                $query="SELECT ID FROM users WHERE name='".$_POST['name']."'
 AND(status='2' OR date > '".time() - (60*60*24) ."')";
 $result=mysql_query($query, $link); [/code]

Returning exactly the same error.

@chazzyJan 11.2006 — #how are you storing your date?

@HellgYauthorJan 11.2006 — #Hmm, date is INT(25), and status is INT(1).

@chazzyJan 11.2006 — #i'll never understand why people don't use the internal date formats. if your column was an actual datetime, for example, you could just use

<i>
 </i>$name=$_POST['name'];
 SELECT ID FROM users WHERE name='".$name."' AND ( status='2' OR date &gt; DATE_SUB(NOW(),INTERVAL 1 DAY))";

i hate using the POST or GET arrays inside of a query, really bad programming in my opinion.

other issues i could think of include date being a keyword or the post array usage may have been throwing it off (not sure)

@HellgYauthorJan 12.2006 — #Hm, I might actually consider that!, What's the format of a DATETIME field?

Is it unix TimeStamp?, I insist to use TimeStamp because otherwise I'm prevented from certain calculations...

@HellgYauthorJan 12.2006 — #OK, so added brackets around the whole expressions and now the calculation works,

i verified that.

Here is the new source:

[code=php]
  $query="SELECT ID FROM users WHERE name='".$_POST['name']."'
 AND(WHERE status='2' OR  WHERE date > '".(time()-60*60*24) ."')";
 $result=mysql_query($query, $link);
 [/code]

And here is the error I'm getting(pretty much of the same thing except the figure):

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE status='2' OR WHERE date > '1136982762')' at line 2
[/QUOTE]
Any ideas?.

@kelly23Jan 12.2006 — #I'm not sure about php and mySQL, but since status is an integer field, shouldn't the quotes around that value be removed from the query?

@HellgYauthorJan 12.2006 — #I can try that even thought out of my experience with phpmyadmin software i can most confidently say that any constant value should be surrounded with brackets.

@kelly23Jan 12.2006 — #Ok, I'm used to asp and Access where that's different. It was just a suggestion.

@HellgYauthorJan 12.2006 — #Thanks!, Any other thoughts anyone?.

@chazzyJan 12.2006 — #Datetime's don't have a format. you format them yourself when pulling the results from the database, the format's similar to the php format of r, but without the timezone switch.

What happens when you issue the query in phpMyAdmin (since you mentioned you used it) as just plain text, no variables? I always test my queries through some sort of case by case basis before trying anything like this. The quotes aren't anything, if you have ints you can skip the quotes but don't need to. Try the query first as just

<i>
 </i>SELECT ID FROM users WHERE name='somename here' AND(status='2' OR  date &gt; '1136982762')

@HellgYauthorJan 12.2006 — #Datetime's don't have a format. you format them yourself when pulling the results from the database, the format's similar to the php format of r, but without the timezone switch.

What happens when you issue the query in phpMyAdmin (since you mentioned you used it) as just plain text, no variables? I always test my queries through some sort of case by case basis before trying anything like this. The quotes aren't anything, if you have ints you can skip the quotes but don't need to. Try the query first as just

<i>
 </i>SELECT ID FROM users WHERE name='somename here' AND(status='2' OR  date &gt; '1136982762')

Also after looking at your query again, I think the biggest problem is that you don't know the syntax for SQL. You only have 1 where clause in a single statement for each query/subquery. you can't repeat the WHERE condition or anything like that.[/QUOTE]
Yesm i took a second look at my sources and detected this problem prior to reading you're code, even thought you fixed this mistake in the previous sources you posted.

Problem solved.

Now facing another problem :p.

Why does the following source returns the errors specified below the source at the marked lines(with '-') accordingly to the order that the erros appear?.

[code=php]
  $query="SELECT * FROM messages WHERE shortcut='registration'";
 $resu1t=mysql_query($query, $link) or die(mysql_error());
 -		 $row=mysql_fetch_array($result); 
 if(!@mail($_POST['email'], $row['title'], $row['message']))
 { 
 //Error handling 
 }
 -	 mysql_free_result($result);
 [/code]

Error:

Warning: mysql_fetch_array(): 5 is not a valid MySQL result resource in e:easyphp1-8wwwunnamed site 2register.php on line 76

Warning: mysql_free_result(): 5 is not a valid MySQL result resource in e:easyphp1-8wwwunnamed site 2register.php on line 85
[/QUOTE]
Any ideas?,

Thanks in advance!.

@chazzyJan 12.2006 — #you're not doing any checking to see that the shortcut specified actually exists. use mysql_num_rows to determine if this item actually exists. if it returns no rows you can't fetch an array of the data in each row.

@HellgYauthorJan 12.2006 — #It does...

Runned this query through PHPmyadmin and it returned exactly what i wanted...

@chazzyJan 12.2006 — #

[code=php]
  $query="SELECT * FROM messages WHERE shortcut='registration'";
 $resu1t=mysql_query($query, $link) or die(mysql_error());
 -		 $row=mysql_fetch_array($result); 
 if(!@mail($_POST['email'], $row['title'], $row['message']))
 { 
 //Error handling 
 }
 -	 mysql_free_result($result);
 [/code]

[/quote]

Just noticed this. Look at your code extremely carefully, you have "$resu1t" as the first varialbe and "$result" as the second variable. change the 1 to an l and it should work.

@omnicityJan 13.2006 — #i read that the function mail() returns TRUE value if it was successful and FALSE if it was not, is it true?.

can i used in the following way to determine whether the user receive the email to his address or not?
[/QUOTE]

No, you can only use this to tell whether the email was sent. IF you got the address wrong, or there is any other failure further down the chain, then this will still return true.

That may be enough for what you want, but it doesn't prove the email was read.

@HellgYauthorJan 13.2006 — #Of course i wasn't expecting to get anything except an initial confirmation , But if the mail was sent and there was no such server as indicated to receive it, will it return FALSE?.

@HellgYauthorJan 13.2006 — #Thanks... It's working...

Well, at least i don't get the same error there any more.

I have two new problems tho..

the following code:

[code=php]
  $query="INSERT INTO users(name, password, date, msn, icq, email, active) VALUES('".$_POST['name']."','".md5($_POST['password'])."','".time()."','".$_POST['msn']."','".$_POST['icq']."','".$_POST['email']."',".time().")";
 $result=mysql_query($query, $link);
 mysql_free_result($result);
 print("success");
 [/code]

Returns this WARNING:

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in e:easyphp1-8wwwunnamed site 2register.php on line 93

[/QOUTE]

With out any reason as far as i can tell... i checked all the possible causes i could think of.

Also the syntax I'm using says that the E-mail address is invalid regardless to the address i enter... Even if it's my one address...

Code:

[code=php]
  $query="SELECT * FROM messages WHERE shortcut='registration'";
 $result=mysql_query($query, $link);
 $row=mysql_fetch_array($result);
 @mail($_POST['email'], $row['title'], $row['message']); 
 if(!(@mail($_POST['email'], $row['title'], $row['message'])))
 {
 if(isset($message))//ERROR HANDLING
 $message=$message."<br>The E-mail address that you indicated is not                             valid";
 else
 $message="The E-mail address that you indicated is not valid";
 $submit=0; 
 }
 [/code]

@omnicityJan 13.2006 — #... the syntax I'm using says that the E-mail address is invalid regardless to the address i enter... Even if it's my one address...

Code:

[code=php]
  $query="SELECT * FROM messages WHERE shortcut='registration'";
 $result=mysql_query($query, $link);
 $row=mysql_fetch_array($result);
 @mail($_POST['email'], $row['title'], $row['message']); 
 if(!(@mail($_POST['email'], $row['title'], $row['message'])))
 {
 if(isset($message))//ERROR HANDLING
 $message=$message."<br>The E-mail address that you indicated is not                             valid";
 else
 $message="The E-mail address that you indicated is not valid";
 $submit=0; 
 }
 [/code]

[/QUOTE]

I think you answered this yourself, without realising. Look at the final [B]if [/B] and you will see that it gives you the same message whether true or false. The [B]else [/B] should be changed to give a 'success message'. Once you have done that we will be able to tell whether the message is being sent or not.

@HellgYauthorJan 13.2006 — #Not quite true...

I'll try to explain my source:

Every time an error occurs i place the error message in the variable...

Or add the error message at the bottom of the variable if a prior message exists...

The variable submit equals 1 before the processing of the page, if any fatal error occurred it's automatically changed to 0. if submit still equals 1 at the bottom of the script $message never gets printed... and the query executed.

The entire source:

[code=php]
 <?php
 function sanitize_input($input) 
 { 
 if(get_magic_quotes_gpc()) 

 $input = stripslashes($input); 

 return(mysql_real_escape_string($input)); 
 } 
 
 If($_SERVER['HTTP_REFERER']==$_SERVER['PHP_SELF'] or isset($_POST['check']))
 {
 if($_POST['name']!=NULL and $_POST['password']!=NULL and  $_POST['password2']!=NULL and 
 $_POST['email']!=NULL and $_POST['email2']!=NULL)
 {
 $submit=1;
 if($_POST['icq']!=NULL and !is_numeric($_POST['icq']))
 {
 if(isset($message))
 $message=$message."<br>Please enter an integer number in the "ICQ" field";
 else
 $message="Please enter an integer number in the "ICQ" field";
 $submit=0;
 } 

 $link=mysql_connect("127.0.0.1", "root", "") or die(mysql_error()); 
 mysql_select_db("nba", $link) or die(mysql_error());
 if(strcmp($_POST['password'], $_POST['password2'])!=0)
 {
 if(isset($message))
 $message=$message."<br>The passwords you entered doesn't match.";
 else 
 $message="The passwords you entered doesn't match.";
 $submit=0;
 } 

 if(strcmp($_POST['email'], $_POST['email2']) != 0)
 {
 if(isset($message))
 $message=$message."<br>The email addresses you have entered doesn't match.";
 else
 $message="The email addresses you have entered doesn't match.";
 $submit=0;
 }
 $_POST['name']=sanitize_input($_POST['name']);
 $_POST['password']=sanitize_input($_POST['password']);
 $_POST['msn']=sanitize_input($_POST['msn']);
 $_POST['icq']=sanitize_input($_POST['icq']);
 $_POST['email']=sanitize_input($_POST['email']); 

 $query="SELECT ID FROM users WHERE name='".$_POST['name']."'
 AND(status='2' OR date > '".(time()-60*60*24) ."')";
 $result=mysql_query($query, $link);
 if(mysql_num_rows($result) == 1)
 {
 if(isset($message))
 $message=$message."<br>The user name that you have choosen is already in                          use";
 else
 $message="The user name that you have choosen is already in use";
 $submit=0;
 }
 mysql_free_result($result) or die(mysql_error());
 $query="SELECT ID FROM users WHERE name='".$_POST['name']."' AND
 (status='2' OR date > '". (time()-60*60*24) ."')"; 

 $result=mysql_query($query, $link);
 if(mysql_num_rows($result))
 {
 if(isset($message))
 $message=$message."<br>The E-mail address that you have entered already                         exsists in the system";
 else
 $message="The E-mail address that you have entered already exsists in the                         system";
 $submit=0;
 }
 mysql_free_result($result); 
 $query="SELECT COUNT(ID)+1 FROM users";
 $result=mysql_query($query, $link);
 $row=mysql_fetch_array($result);
 $userid=$row[0]; 

 mysql_free_result($result);
 $query="SELECT * FROM messages WHERE shortcut='registration'";
 $result=mysql_query($query, $link);
 $row=mysql_fetch_array($result);
 @mail($_POST['email'], $row['title'], $row['message']); 
 if(!(@mail($_POST['email'], $row['title'], $row['message'])))
 {
 if(isset($message))//ERROR HANDLING
 $message=$message."<br>The E-mail address that you indicated is not                             vaild";
 else
 $message="The E-mail address that you indicated is not vaild";
 $submit=0; 
 }
 mysql_free_result($result);
 if($submit==1)
 { 
 $query="INSERT INTO users(name, password, date, msn, icq, email, active)                     VALUES('".$_POST['name']."','".md5($_POST['password'])."','".time()."','".
 $_POST['msn']."','".$_POST['icq']."','".$_POST['email']."',".time().")";
 $result=mysql_query($query, $link);
 mysql_free_result($result);
 print("success");
 } 
 }
 else
 print("Please fill out all the fields marked with "*""); 
 if(isset($message))
 print($message); 
 }
 
 ?>
 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 <html xmlns="http://www.w3.org/1999/xhtml">
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=" />
 <title>Untitled Document</title>
 </head>
 
 <body>
 <form method="post" action="<?php print($_SERVER['PHP_SELF']);?>">
 <table width="70%" border="0" cellspacing="1" cellpadding="0" align="right" dir="rtl">
 <tr>
 <td width="20%">User name*</td>
 <td width="30%"><input type="text" dir="rtl" align="right" name="name" maxlength="12" size="20"/></td>
 <td width="20%">Retype E-mail* </td>
 <td><input type="text" dir="rtl" align="right" name="email2" maxlength="30" size="20"/></td>
 
 </tr>
 <tr>
 <td>password*</td>
 <td><input type="password" dir="rtl" align="right" name="password" maxlength="20" size="20"/>
 </td>
 <td width="20%">Msn messengr ID</td>
 <td><input type="text" dir="rtl" align="right" name="msn" maxlength="20" size="20"/></td>
 
 </tr>
 <tr>
 <td>retype password*</td>
 <td><input type="password" dir="rtl" align="right" name="password2" maxlength="20"  size="20"/></td>
 <td>ICQ number </td>
 <td><input type="text" dir="rtl" align="right" name="icq" maxlength="12" size="20"/></td>
 
 </tr>
 <tr>
 <td width="20%">Contact E-mail*</td>
 <td><input type="text" dir="rtl" align="right" name="email" maxlength="30" size="20"/> </td>
 
 </tr>
 <tr><td><input type="submit" dir="rtl" align="right" value="send" /></td></tr>
 </table>
 <input type="hidden" name="check" value="1" /> 
 </form>
 </body>
 </html>
 [/code]

@omnicityJan 13.2006 — #
Not quite true...

I'll try to explain my source:

Every time an error occurs i place the error message in the variable...

Or add the error message at the bottom of the variable if a prior message exists...
[/QUOTE]
Now I understand - this isn't the nicest looking construction I have ever seen!

Seing it all in context helps a lot.

[code=php]
  @mail($_POST['email'], $row['title'], $row['message']); 
 if(!(@mail($_POST['email'], $row['title'], $row['message'])))
 [/code]

[/QUOTE]

Don't these two lines do the same thing twice? (Then test the second time, obviously.)

@HellgYauthorJan 13.2006 — #Yes, I removed the mail() function from the if condition and placed it on the line above... to see whether it's sending an email message and just nor qualified to be used in if condition or just not receiving proper arguments.

I just forgot to remove the line i used for testing

I got my answer...

I probably just don't handle the query results properly!,

any advices guys?.

Also in #PHP _↴

transferring sessions http_get File download problem in https site

Success!

Help @HellgY spread the word by sharing this article on Twitter...

Tweet This

A query returns error and don’t behaves as expected.

23 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

A query returns error and don’t behaves as expected.

23 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

23 Comments(s) _↴

Also in #PHP _↴