[Man2u2uk]

i need to make a search functon to bring up the exact match or similar...

i have four tables in my database

• music_albums
  
• music_singles
  
• movies
  
• games

i already have made a search box which links to search.php

in this search.php file could i put something like:

$query="SELECT * FROM music_albums, music_singles, movies, games WHERE prod_name, prod_artist LIKE";

i am not sure?

[benbacardi]

What's the search box called? I think they'd have to be seperate querys per table but I'm not sure, for example:

ALBUM RESULTS:
(then list results of "SELECT * FROM music_albums WHERE prod_name LIKE '%".$_POST['seach']."%' OR prod_artist LIKE '%".$_POST['search']."%'" )

SINGLE RESULTS:
etc

Where $_POST['search'] is the reference to whatever the user typed in

[Ben Abrams]

no, do one query but with 3 joins added:

Have a look on W3c schools and see if that helps.

[benbacardi]

But what would you join the tables on if they're three seperate (non-relational) tables Ben?

[Ben Abrams]

If you had a similar field, like "description" or "search tags" i would do a LIKE statement on the 4 columns.

Or loop the similar fields into an array, and then count the frequency of the search terms in the array value. Then you could order them by relevence, with a little code.

Be a lot more server side work but you would get more accurate results.

[Man2u2uk]

Would the following work?

SELECT * FROM games, movies, music_singles, music_albums
WHERE prod_artist LIKE '%$Search%'";

• i am searching all of my tables

• using the data that has been entered in the search box. ( '%$Search%'")

• for anything that looks like somthing in prod_artist

[benbacardi]

No, because you're not telling the database *which* prod_name to search

[Man2u2uk]

lol you lost me now, i am confused

bascially i just need to search for products using either there "prod_id" or "prod_title"

here is my search form.. it should link to search.php.

<form action="search.php" method="post">
<div>
<input type="text" name="search" size="14" value="Product Search..."/>
<input name="submit" type="submit" value="Go"/>
</div>
</form>

and then in search.php is where the code is to run the search and show the results.

SELECT * FROM games, movies, music_singles, music_albums
WHERE prod_artist LIKE '%$Search%'";

[benbacardi]

Yes, but the SQL won't know which of the tables (games, movies, music_singles) etc it has to look in for "prod_artist"

You need to tell it that by prefixing prod_artist with the table name: games.prod_artist, or movies.prod_artist etc

[Man2u2uk]

Like this?


SELECT * FROM games.prod_artist, movies.prod_artist, music_singles.prod_artist, music_albums.prod_artist
WHERE prod_artist LIKE '%$Search%'";

[benbacardi]

Nope! SELECT * FROM games, movies, music_singles, music_albums WHERE games.prod_artist LIKE '%$Search%'" OR movies.prod_artist LIKE '%$Search%'" OR music_singles.prod_artist LIKE '%$Search%'" OR music_albums.prod_artist LIKE '%$Search%'"

[Man2u2uk]

Thanks ben,

as you can tell i am a complete PHP n00b

i have managed to get it working. had to edit it a bit.

$query="SELECT * FROM games, movies, music_singles, music_albums WHERE games.prod_artist LIKE '%{$_POST['search']}%' OR movies.prod_artist LIKE '%{$_POST['search']}%' OR music_singles.prod_artist LIKE '%{$_POST['search']}%' OR music_albums.prod_artist LIKE '%{$_POST['search']}%'";

but for some reason it will only bring up data from the games table?

This post has been edited by Man2u2uk: 19 April 2007 - 05:50 PM

[benbacardi]

yea, just before the sql put

$search = $_POST['whereyoursearchis'];

[Man2u2uk]

i am guessing i need to put a " at the start and end like so,

$query="SELECT movies.*, music_singles.*, albums.*, games.*,
FROM movies_table, music_table,albums_table, games_table
WHERE games.prod_artist LIKE '%{$_POST['search']}%' OR movies.prod_artist LIKE '%{$_POST['search']}%' OR music_singles.prod_artist LIKE '%{$_POST['search']}%' OR music_albums.prod_artist LIKE '%{$_POST['search']}%'";

but when i preview this it says:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM movies_table, music_table,albums_table, games_table WHERE

This post has been edited by Man2u2uk: 20 April 2007 - 08:08 AM

[Karl Buckland]

You don't need the comma before FROM.

[Man2u2uk]

ok so now i have

$query="SELECT movies.*, music_singles.*, music_albums.*, games.* FROM movies_table, music_table,albums_table, games_table
WHERE games.prod_artist LIKE '%{$_POST['search']}%' OR movies.prod_artist LIKE '%{$_POST['search']}%' OR music_singles.prod_artist LIKE '%{$_POST['search']}%' OR music_albums.prod_artist LIKE '%{$_POST['search']}%'";

i am now getting the following error:

Table 'db_337706.movies_table' doesn't exist

below shows the tables in my database
[attachment=246:Untitled_1.jpg]

below shows what is inside each table.
[attachment=247:Untitled_2.jpg]

(feel free to abuse my if my mysql and php is wrong, i am a n00b at this)

[benbacardi]

What is listed after the FROM keyword must be the names of the tables. You don't have a table called "games_table" - it's just called "games", similarly for movies. Your tables aren't called "albums_table" but "music_albums" etc etc...

On a different note, is each table identical? If so, I would like to suggest a nicer much much much much easier to search technique. One table, called "products", with those fields inside it, but an extra field that simply specifies if it is a "game" a "movie" an "album" or a "single"...

That would be so much simpler and easier to code

[Man2u2uk]

Thanks ben,

i have now converted all 4 tables into 1 and i made a new field called prod_cat and thats where i put wheather it is a game, movie, album or a single..

now how can i make my search more simple?

[benbacardi]

SELECT * FROM name_of_table WHERE prod_artist LIKE '%{$_POST['search']}%' OR prod_name LIKE '%{$_POST['search']}%'

Substitute name_of_table with the name of your table

[Man2u2uk]

Woot works like a dream

the following code:

<? echo '<a href ="'.$_SERVER['PHP_SELF'].
'?buy='.$i.'">Add</a>';?> </div>

places the word "Add" next to each procuct, when "Add" is clicked it adds the product to my basket which works fine...

how can i get it so that after clicking on "Add" it will display a message saying "You have just added (item) to you cart"