[RESOLVED] sql query and php problem

panosgr

Hi from Athens Greece,

I'm a newbie in php and database and I have a problem with a mysql query in a php script.

The script:
[INDENT]

<html>
<body>
<?php
$username="user1";
$password="pass1";
$database="database1";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");



$query= "set @eponymia='', @kod_eisp='', @imnia_eisp='', @eisp_poso='', @ypol='';
SELECT kod_pelati, 
if(eponymia=@eponymia,\'>>\', eponymia)eponymia,
kod_xre, 
imnia_xre, 
poso_xre, 
if(kod_eisp=@kod_eisp,\'-\', kod_eisp)kod_eisp, 
if(imnia_eisp=@imnia_eisp,\'-\', imnia_eisp)imnia_eisp,
if(poso_eisp=@poso_eisp,\'-\', poso_eisp)poso_eisp, 
if(ypol=@ypol,\'-\', ypol)ypol,
@kod_eisp:=kod_eisp, 
@eponymia:=eponymia, 
@imnia_eisp:=imnia_eisp, 
@poso_eisp:=poso_eisp, 
@ypol:=ypol
FROM kartela";

$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Value1</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value2</font></th>
…..
<th><font face="Arial, Helvetica, sans-serif">Value13</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value14</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {

$f1=mysql_result($result,$i,"field1");
$f2=mysql_result($result,$i,"field2");
….
$f13=mysql_result($result,$i,"field13");
$f14=mysql_result($result,$i,"field14");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
….
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f13; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f14; ?></font></td>
</tr>

<?php
$i++;
}
?>
</body>
</html>

[/INDENT]

The result in my browser is a warning and a blank page
“ Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\panos\panos-kartela.php on line 32 “

Another strange thing is that the sql query works very well in phpmyadmin.

Any help will be really very appreciated.

Thanks

djjjozsi

You should use:

$result=mysql_query($query) or die(mysql_error()  ."in query:".htmlspecialchars($query)  )

mysql_numrows is not a valid function to get the result number.
you mean: mysql_num_rows ?

In the connection: locathost shluld be a string "localhost"

@ is to hide the error reporting if you don't have the connection (because it has failed).
remove it and use a better way to switch ON/OFF the error reporting.

like:

	error_reporting(E_ALL);
ini_set("display_errors", 1);

You should close a mysql connection at the very end of your program.

And see the examples on the mysql_fetch_assoc for a better way to retrieve the data from your results:
mysql_fetch_assoc()

panosgr

Hi djjjozsi and thank you for your quick answer.
I made the needed modification so my script is like that now:

 <html>
<body>
<?php
$username="user1";
$password="pass1";
$database="database1";

mysql_connect("localhost",$username,$password);
mysql_select_db($database) or die( "Unable to select database");



$query= "set @eponymia='', @kod_eisp='', @imnia_eisp='', @eisp_poso='', @ypol='';
SELECT kod_pelati,
if(eponymia=@eponymia,\'>>\', eponymia)eponymia,
kod_xre,
imnia_xre,
poso_xre,
if(kod_eisp=@kod_eisp,\'-\', kod_eisp)kod_eisp,
if(imnia_eisp=@imnia_eisp,\'-\', imnia_eisp)imnia_eisp,
if(poso_eisp=@poso_eisp,\'-\', poso_eisp)poso_eisp,
if(ypol=@ypol,\'-\', ypol)ypol,
@kod_eisp:=kod_eisp,
@eponymia:=eponymia,
@imnia_eisp:=imnia_eisp,
@poso_eisp:=poso_eisp,
@ypol:=ypol
FROM kartela";

$result=mysql_query($query) or die(mysql_error()  ."in query:".htmlspecialchars($query)  );





$num=mysql_num_rows($result);

mysql_close();
?>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Value1</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value2</font></th>
…..
<th><font face="Arial, Helvetica, sans-serif">Value13</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value14</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {

$f1=mysql_result($result,$i,"field1");
$f2=mysql_result($result,$i,"field2");
….
$f13=mysql_result($result,$i,"field13");
$f14=mysql_result($result,$i,"field14");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
….
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f13; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f14; ?></font></td>
</tr>

<?php
$i++;
}
?>
</body>

nad i I got an sql error:

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 'SELECT kod_pelati, if(eponymia=@eponymia,\'>>\', eponymia)eponymia, kod_xre, ' at line 2in query:set @eponymia='', @kod_eisp='', @imnia_eisp='', @eisp_poso='', @ypol=''; SELECT kod_pelati, if(eponymia=@eponymia,\'>>\', eponymia)eponymia, kod_xre, imnia_xre, poso_xre, if(kod_eisp=@kod_eisp,\'-\', kod_eisp)kod_eisp, if(imnia_eisp=@imnia_eisp,\'-\', imnia_eisp)imnia_eisp, if(poso_eisp=@poso_eisp,\'-\', poso_eisp)poso_eisp, if(ypol=@ypol,\'-\', ypol)ypol, @kod_eisp:=kod_eisp, @eponymia:=eponymia, @imnia_eisp:=imnia_eisp, @poso_eisp:=poso_eisp, @ypol:=ypol FROM kartela

I repeat that the sql query works perfectly well in phpmyadmin.

Thank you for any (really needed) help.

Panos

djjjozsi

i wanted to lead you to the technique how to trouble shoot mysql queries.

As you see, a backslash precedes the apostrophes. Lets correct the query.

johanafm

panosgr;10936867 wrote:
I repeat that the sql query works perfectly well in phpmyadmin.

QuerIES.

$query1 = 'first query';
$query2 = 'second query';

mysql_query($query1);
mysql_query($query2);

panosgr

Hi johanafm,

What do you mean by queries?

I think that in my script it's only one query . Well a query and a subquery.

I thank you for any help. I need to made this script working!

Panos

johanafm;10936870 wrote:

QuerIES.

$query1 = 'first query';
$query2 = 'second query';

mysql_query($query1);
mysql_query($query2);

panosgr

Hi again djjjozsi,

I really dont see any correction that i can made to my query.
Maybe you can give me more hints?

Thanks

Panos

djjjozsi;10936869 wrote:
i wanted to lead you to the technique how to trouble shoot mysql queries.

As you see, a backslash precedes the apostrophes. Lets correct the query.

johanafm

panosgr;10936877 wrote:
What do you mean by queries?

Plural, as in several queries, not one query.

Well, I suppose the correct term would be SQL statement. Query is probably mainly used to denote insert, update, delete and select statements. Anyway, your call to mysql_query contains two statements: SET and SELECT. This is not allowed (by mysql_query; the MySQL dbms has no problems with it). As such you get a syntax error:

the right syntax to use near 'SELECT (...)'

The correct syntax is removing all of it, from and including the ; and perform two separate queries.

djjjozsi's comment about escaped backslashes is due to the usage of ' as a string delimiter. To use the character ' inside a string, you need to escape the character. But you NEVER escape a delimiter.

-- This is a string containing a ' character
'\''

And while I've been using MySQL exclusively for far too long, iirc the SQL standard says you escape the ' character with '. That is, a string containing the ' character would be

''''

Whatever the case, in MySQL the escape character is user defined. \ is nothing but the default escape character. As such, you should not escape characters yourself, but rather

mysql_connect();	# this is important. since escaping depends on the db setting, you must connect first.
$escaped_str = mysql_real_escape_string($str);

Moreover, you don't escape the whole statement, since you would then once again be escaping string delimiters. You escape arguments.

Also note the difference between escaping characters for PHP's sake

# neither ' is escaped when it concerns MySQL if you sent these strings as queries.
$str = "this is an apostrophe: '";
$str = 'this is exactly the same: \'';

and MySQL's

$str = "This apostrophe is escaped as far as MySQL is concerned: \\'";
$str = 'This is exactly the same: \\\'';

panosgr

Hi johanafm,

Thank you very much for your help.
In fact you was right. The main problem was the number of SQL statements.
I split them in 2 and the script is working very well with your help.
Once again thank you very much

Panos

johanafm;10936888 wrote:
Plural, as in several queries, not one query.

Well, I suppose the correct term would be SQL statement. Query is probably mainly used to denote insert, update, delete and select statements. Anyway, your call to mysql_query contains two statements: SET and SELECT. This is not allowed (by mysql_query; the MySQL dbms has no problems with it). As such you get a syntax error:
the right syntax to use near 'SELECT (...)'
The correct syntax is removing all of it, from and including the ; and perform two separate queries.

djjjozsi's comment about escaped backslashes is due to the usage of ' as a string delimiter. To use the character ' inside a string, you need to escape the character. But you NEVER escape a delimiter.
-- This is a string containing a ' character
'\''
And while I've been using MySQL exclusively for far too long, iirc the SQL standard says you escape the ' character with '. That is, a string containing the ' character would be
''''
Whatever the case, in MySQL the escape character is user defined. \ is nothing but the default escape character. As such, you should not escape characters yourself, but rather
mysql_connect();	# this is important. since escaping depends on the db setting, you must connect first.
$escaped_str = mysql_real_escape_string($str);
Moreover, you don't escape the whole statement, since you would then once again be escaping string delimiters. You escape arguments.

Also note the difference between escaping characters for PHP's sake
# neither ' is escaped when it concerns MySQL if you sent these strings as queries.
$str = "this is an apostrophe: '";
$str = 'this is exactly the same: \'';
and MySQL's
$str = "This apostrophe is escaped as far as MySQL is concerned: \\'";
$str = 'This is exactly the same: \\\'';