[Resolved] What is wrong with this?

jamesm87

Here is my mySQL query in PHP

$sql="select * from BPB_friends,BPB_online where BPB_friends.friendname=BPB_online.loginname id=('$id') ";
$result=mysql_query($sql);	
$num = mysql_num_rows($result);

And each time it gives off this error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/hollyfie/bpb/friend.php on line 9

The script is ment to be a friends list, so in each table

BPB_friends:

ID (ID of the owner of the friends list
freindsname (Name of the friend name added)
block (is this person blocked?)

BPB_online

ID (The Friend added ID)
loginname (the friend added name)
online (the friend added online or not)

any idea's to fix it or to make it better and easier.

stolzyboy

change this line

$result=mysql_query($sql);

to this

$result=mysql_query($sql) or die(mysql_error());

it will then tell what is wrong with the query, usually when you get that error, your query is bad and/or is empty/returning nothing/etc...

jamesm87

You have an error in your SQL syntax near 'id=('1')' at line 1

is what is given... make any sense?

stolzyboy

you need to use AND in between your WHERE clauses

WHERE something = $something AND another = $another

mattr

WHERE BPB_friends.friendname=BPB_online.loginname
AND ID = $id

stolzyboy

Originally posted by mattr
WHERE BPB_friends.friendname=BPB_online.loginname
AND ID = $id

yep, pretty much what i told him

make sure and mark this resolved when you got it working

mattr

Originally posted by stolzyboy
yep, pretty much what i told him

make sure and mark this resolved when you got it working

It's important to not only note the lack of 'and' but point out that where id = ('1') is wrong, too.

stolzyboy

Originally posted by mattr
It's important to not only note the lack of 'and' but point out that where id = ('1') is wrong, too.

and why is ('1') wrong, the parentheses will not hurt anything

mattr

If ID is a number, and I suspect it is, then '1' is comparing a string to a numeric. This is allowed in MySQL but is in violation of the SQL specifications. There is no justification for doing so except being needlessly non-standard.

Secondly, the parens are also non-standard.

Just because it 'works' in MySQL doesn't make it right.

stolzyboy

well since he IS using mysql, it doesn't matter, and since when are parens not allowed, they are for GROUPING, and quoting does not hinder anything either, integer or not

jamesm87

Ok... I have changed it by adding a 'and' and now recieve this error

Column: 'id' in where clause is ambiguous

Perhaps what mattr said maybe right but I do not know what to change from his explanation

Any idea's

James
ps.. thanks in advance ;-)

mattr

If you have an ID in both tables you will have to qualify it with a table name, e.g. BPB_friends.ID or BPB_online.ID

stolzyboy

what he said, mysql doesn't know which one to use, since you are selecting from two different tables, hence "ambiguous"

jamesm87

Ok thankyou... I will try it now... brb (5mins)

jamesm87

Absolutly Perfect! Thankyou stolzyboy and mattr for your help

James

jamesm87

Another slight problem... this is the PHP I have done:

$sql10="select * from BPB_chat WHERE to = $myname";
$result10=mysql_query($sql10) or die(mysql_error());	
$num10 = mysql_num_rows($result10);
$all10= mysql_fetch_array($result10);

and it gives this error

You have an error in your SQL syntax near 'to = admin' at line 1

The variable $myname is got by a different SQL. I will give you this just incase you need it 🙂

$sql4="select loginname as myname from BPB_login WHERE id = $id";
$result4=mysql_query($sql4) or die(mysql_error());
$all4= mysql_fetch_array($result4);
 extract($all4);

any idea's?

Thanks in advance

James😃

mattr

$myname is a string, therefore you need to enclose it in single quotes, e.g. where name = '$name'.

You also need to secure your query against SQL injection attacks (and run of the mill single quotes) by running it through addslashes.

jamesm87

Ok thanks! Is there anything wrong with this because I get this error

You have an error in your SQL syntax near 'from,touser,chatlog) VALUES ('1','admin','katiem89','.')' at line 1

this is the SQL query

insert into BPB_chat (chatid,from,touser,chatlog) VALUES ('$chatno','$myname','$to','.')

mattr

FROM is a SQL reserved word. You should change the column name, but if you insist on using it you must put it in backticks (I think) to tell mySQL that it's 'special'.