assign alias problems

[Taylor_1978]

Howdy...

I am using the following as a reference:

http://dev.mysql.com/doc/refman/5.0/en/select.html

Where it discusses assigning alias, using CONCAT.

This is what I am doing:


$s2 = mysql_query("SELECT CONCAT(`fname`,' ',`lname`) AS `full_name` FROM `mem_users` WHERE `full_name`='".$_REQUEST['names']."'") or die(mysql_error());


Unfortunately though I keep getting the error of Unknown column 'full_name' in 'where clause'.

Any help appreciated!

:thumbsup:

[abduraooft]

mysql_query("SELECT CONCAT(`fname`,' ',`lname`) AS
`full_name` FROM `mem_users`
HAVING `full_name`='".$_REQUEST['names']."'") or die(mysql_error());

PS: Your query is likely to yield sql injection. Make use of mysql_real_escape_string()

[Taylor_1978]

mysql_real_escape_string() - That's for special characters isn't it? As I stop any characters other than alphabetic being inputted.

And thanks for the fix.. works a treat now. Am just using a mysql_num_rows to find out if there is a match or not.

[abduraooft]

mysql_real_escape_string() - That's for special characters isn't it? As I stop any characters other than alphabetic being inputted. If you have sufficient validation, then it's Okay. But what if the user searches for a name like O'Reilly or O'Brien? :)

[Taylor_1978]

Oh yup - good point!

Bare with me - I haven't used this before. Reading it now.. but unsure of how I should use this with my query, when just using it to count number of valid rows.

[abduraooft]

If magic_quote_gpc is off(should be!) in your server then you could just use it like mysql_query("SELECT CONCAT(`fname`,' ',`lname`) AS
`full_name` FROM `mem_users`
HAVING `full_name`='".mysql_real_escape_string($_REQUEST['names'])."'") or die(mysql_error());
See http://php.net/mysql_real_escape_string
when just using it to count number of valid rows.
If you just need the number of rows then your query should be
"SELECT count(*) AS row_count
FROM `user`
where concat( `first_name` , ' ', `last_name` ) = '".mysql_real_escape_string($_REQUEST['names'])."'" and you don't have to use mysql_num_rows().

[Taylor_1978]

Okay - so now I'm a bit further confused.. I just kind of replaced things with what would make sense to me in php.. but it didn't quite work! lol


$n2 = mysql_query("SELECT count(*) AS row_count FROM `mem_users` where concat( `fname` , ' ', `lname` ) = '".mysql_real_escape_string($_REQUEST['names'])."'") or die(mysql_error());


Which gave me: Resource Code #9

[abduraooft]

Which gave me: Resource Code #9 Yeah, that's normal when you echo the return value of mysql_query(). You need to fetch the values from the result, like
$row=mysql_fetch_assoc($n2);
echo $row['row_count'];

[Taylor_1978]

Ahh okay, of course - I confused myself a bit there.. See it was the non-usage of mysql_num_rows that threw me.

So for the purpose of learning, how does using


$n2 = mysql_query("SELECT count(*) AS row_count
FROM `mem_users`
where concat( `fname` , ' ', `lname` ) =
'".mysql_real_escape_string($_REQUEST['names'])."'")
or die(mysql_error());
$row=mysql_fetch_assoc($n2);


benefit as opposed to using


$n2 = mysql_query("SELECT *
FROM `mem_users`
where concat( `fname` , ' ', `lname` ) =
'".mysql_real_escape_string($_REQUEST['names'])."'")
or die(mysql_error());
$row=mysql_num_rows($n2)


Cheers!

[abduraooft]

To use mysql_num_rows(), you need to fetch all the (matching) rows from the table. If the purpose is just to find the count, then the query would be very inefficient. On the other hand, the function count() simply find the total number of (matched) rows and returns only a single value.

The important point to be kept in mind is that the query with count() will return a single row (no match => column having value 0 ; match =>column having a value equal to the number of matched rows), and thus applying mysql_num_rows() on the query result always gives a value 1.

PS: Check the use of count() in some other contexts from http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html

[Taylor_1978]

Great! Makes sense.. I guess without having full knowledge of count() - I wasn't understanding how it was more efficient.. But that makes a lot of sense.. I use mysql_num_rows quite a bit - so I'll do some reading up on count() as suggested!

Thanks Abduraooft... A great help as always!