Joining tables in sql query for a search
User1874212’s 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'”
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.
