Joining tables in sql query for a search

Question

Alright this is a total mess and I'm so lost right now. I'm trying to have a search for a site in which you can search for a word and it will go through a table called designs to search the table through multiple columns for the word. I had that working until I decided that I would like the option to also search for the username of another member. in the design table, there is a column named user in which that matches up with the id inside of users. I hope that makes sense because I really don't want to have to draw out a table..

$result = mysql_query("
SELECT designs.*, users.* 
FROM designs, users 
WHERE type LIKE '%" . mysql_real_escape_string($search1) . "%' 
OR title LIKE '%" . mysql_real_escape_string($search1) . "%' 
OR tags LIKE '%" . mysql_real_escape_string($search1) . "%' 
OR description LIKE '%" . mysql_real_escape_string($search1) . "%' 
OR users.username = " . mysql_real_escape_string($search1) . " 
ORDER BY designs.id 
DESC
");

This is what it currently looks like, and $search1 is what the user is searching.. I want to be able to display any designs that a user has made by searching for the username of the user after connecting the designs.user to users.id but it just won't work, any ideas?

ALSO mysql_error() gives me the error that "Unknown column 'namehere' in 'where clause'"

Starx · Accepted Answer · 2013-07-21 01:06:21Z

1

The query for inner join is this, and you have to specify the table name like designs.<columnname> to query them.

    SELECT designs.*, users.* 
    FROM designs
    INNER JOIN users on users.id = designs.user
    WHERE designs.type LIKE '%<SEARCH TEXT>%' 
    OR desings.title LIKE '%<SEARCH TEXT>%' 
    OR designs.tags LIKE '%<SEARCH TEXT>%' 
    OR designs.description LIKE '%<SEARCH TEXT>%' 
    OR users.username = '<USER ID>' 
    ORDER BY designs.id 
    DESC

Simple syntax is

INNER JOIN <table name> <alias> ON <first table>.<column name> = <second table>.<column name>

HUGE Problem is you are using `mysql_*` API, please these method are very vulnerable Do not USE them

If you use other API like mysqli or PDO, you dont have to use mysql_real_escape_string to make your query safe.

edited Jul 21, 2013 at 1:06

answered Jul 21, 2013 at 1:00

Starx

79.3k50 gold badges187 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

user1874212 Over a year ago

it is giving me the same error... and I was going to look into changing that stuff after I got it all working p: which may seem a little backwards but I am still learning and I at least know some mysql

Starx Over a year ago

@user1874212, What is the error it is giving you, if you the fields and column name are appropriate the above query should work.

user1874212 Over a year ago

"Unknown column 'what i searched for' in 'where clause'"

user1874212 Over a year ago

also maybe i should clarify that users.username would be the same as the search text.. the id would be users.id

Starx Over a year ago

When you get a that type of error that mean the column name does not matches to the database, Can you verify that? That is your part to do not mine I am afraid.

|

Collectives™ on Stack Overflow

Joining tables in sql query for a search

1 Answer 1

HUGE Problem is you are using `mysql_*` API, please these method are very vulnerable Do not USE them

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

HUGE Problem is you are using mysql_* API, please these method are very vulnerable Do not USE them

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related

HUGE Problem is you are using `mysql_*` API, please these method are very vulnerable Do not USE them