PHP sql query help.

[iansteeze]

I cant seem to get this to work correctly.

I am writing a little search query for my script but i want it to check multiple columns not just one.

This is the code for the query

$sql = "SELECT a.*, u.user_id, u.username, u.user_colour from ".CLASSIFIEDS_TABLE." a, ". USERS_TABLE ." u 
				WHERE a.ad_description OR a.ad_title OR u.username LIKE '%$search%' and u.user_id = a.ad_poster_id ORDER BY a.ad_date DESC";

Rite now it is only looking in the u.username column not the other

Any ideas on a fix for this?

[iansteeze]

I think i figured out i have to do it like :

$sql = "SELECT a.*, u.user_id, u.username, u.user_colour from ".CLASSIFIEDS_TABLE." a, ". USERS_TABLE ." u 
WHERE a.ad_description LIKE '%$search%' and u.user_id = a.ad_poster_id 
OR a.ad_title LIKE '%$search%' and u.user_id = a.ad_poster_id 
OR u.username LIKE '%$search%' and u.user_id = a.ad_poster_id 
ORDER BY a.ad_date DESC";

but maybe there is a better way?

[r937]

but maybe there is a better way?

there sure is

SELECT a.*
     , u.user_id
     , u.username
     , u.user_colour 
  FROM ".CLASSIFIEDS_TABLE." a
INNER
  JOIN ". USERS_TABLE ." u 
    ON u.user_id = a.ad_poster_id 
 WHERE a.ad_description LIKE '%$search%' 
    OR a.ad_title LIKE '%$search%' 
    OR u.username LIKE '%$search%' 
ORDER 
    BY a.ad_date DESC

[iansteeze]

Thanks that works great also


Another question i have is can i make it so the query is not case sensitive?

For example when i search "Welcome" i get a result but when i search "welcome" i get nothing.

[r937]

The default character set and collation are latin1 and latin1_swedish_ci, so non-binary string comparisons are case insensitive by default.
-- http://dev.mysql.com/doc/refman/5.0/...nsitivity.html

helps?

[lampcms.com]

Thanks that works great also


Another question i have is can i make it so the query is not case sensitive?

For example when i search "Welcome" i get a result but when i search "welcome" i get nothing.

Normally it should already be non case sensitive
You probably have a case-sensitive column types like BINARY, VARBINARY, or BLOB
so if you change these to CHAR, VARCHAR, or TEXT, the case-insensitive searches will also work

[iansteeze]

helps?

Thanks that helped a but but i think i have no choice but to use UTF8_bin so i might be out of luck on that

[iansteeze]

Normally it should already be non case sensitive
You probably have a case-sensitive column types like BINARY, VARBINARY, or BLOB
so if you change these to CHAR, VARCHAR, or TEXT, the case-insensitive searches will also work

The column type is LONGTEXT would that be case sensitive over just TEXT ?

[iansteeze]

I solved it by using strtolower() and LOWER()

[SituationSoap]

Two points: you should never select * from a table, as you run the risk of the table changing in the future, and your code not being prepared to handle it.

Also: You should never concatenate variables directly into your SQL. You should Parameterize them (http://us.php.net/mysqli) in order to avoid anyone maliciously or unintentionally corrupting or deleting your database.

[iansteeze]

Two points: you should never select * from a table, as you run the risk of the table changing in the future, and your code not being prepared to handle it.

Also: You should never concatenate variables directly into your SQL. You should Parameterize them (http://us.php.net/mysqli) in order to avoid anyone maliciously or unintentionally corrupting or deleting your database.

I just had * for testing and will be changed.

Most of the variables are taken care of before used int he SQL but some are escaped with $db->sql_escape()