[SOLVED] can I run a select statement based on array values?

[xtiancjs]

Hi,

I have an array that has x number of values. I want to run a sql statement x number of times using the next array value each time.

Basically I have a query that displays results based on a search. The result of the search is a list of names, the trouble is for reasons beyond my control the names are in a single db field as "FirstLast". I want to take this value and match it up to another table that has the first and last names in separate fields. So far I have extracted the results from the first query using a while loop, the resulting array is called $name_search :

 

mysql_select_db($database_nancy, $nancy);

$query_search = "SELECT artist FROM images WHERE caption LIKE '%$keyword%' OR piece_name LIKE '%$keyword%' OR show_title LIKE '%$keyword%' GROUP BY artist";

$search = mysql_query($query_search, $nancy) or die(mysql_error());

$totalRows_search = mysql_num_rows($search);

$name_search = array();

while(list($artist) = mysql_fetch_row($search)) {

$name_search[] = $artist;

}

 

I now want to take the values of the $name_search array and run a SELECT statement on table2 for each value and display the results. Could a foreach loop handle that ?

[Psycho]

There must be something in the water. I have seen numerous posts lately  concerning queries within loops of other query results.

 

If you were to post the 2nd query you intend to use, I'm sure that I or someone else could provide you a single query that would get all the records you want in one go. That is the whole pont of having a relational database.

[xtiancjs]

Thanks for the reply

 

My second query would be something along the lines of

 

SELECT first_name, last_name FROM artists WHERE CONCAT(first_name,last_name) ='$name_search'  or something along those lines.

 

I originally tried a few queries trying to get results from both tables all similar to:

 

SELECT images.artist, artists.first_name, artists.last_name FROM images, artists WHERE images.caption LIKE '%$keyword%' OR images.piece_name LIKE '%$keyword%' OR images.show_title LIKE '%$keyword%'  AND CONCAT(artists.first_name,artists.last_name) = images.artist GROUP BY images.artist

 

the server this is on is using mysql version 3.23.58 (I obviously wish they would update soon)

 

Does this help?

[Psycho]

Are you saying that you have two tables (images & artists) and that one table (images) uses the full name, while the other table (artists) has distinct fields for first name and last name? Do you not have some kind of id field that ties the two tables together???

 

If you did you could get all your data with a single query like this:

 

 

SELECT artists.first_name, artists.last_name

 

FROM artists

LEFT JOIN images

  ON artists.artist_id = images.artist_id

 

WHERE images.caption LIKE '%$keyword%'

  OR images.piece_name LIKE '%$keyword%'

  OR images.show_title LIKE '%$keyword%'

[xtiancjs]

Unfortunately the database tables were designed pre my involvement , There is no common id between the 2 tables.

[Psycho]

Then you should create one instead of adding to the problem. Determine what the unique field is for artists and populate a new column in images with that value associated with the artist.

 

Could you post the fields for both of those tables?

[xtiancjs]

That makes sense, will give it a shot

 

here is the field info

 

Table images:

        id (primary key), artist, caption, name (file name of an image), image_name

 

Table artists:

      id (primary key), first_name, last_name

 

 

 

[Psycho]

I would suggest this:

 

Create a column in the images table called artist_id and then populate it with something like this:

 

$query = "SELECT * FROM artists"
$result = mysql_query($query);

//Loop through each artist record
while ($row = mysql_fetch_assoc($result)) {
  $fullName = $row[first_name] . ' ' . $row[last_name];
  $query = "UPDATE images SET artist_id = '$row[id]' WHERE artist = '$fullName';
}

 

Of course you should test this before actually running it.

[xtiancjs]

Thanks for all your help, I did basically the same thing via the terminal and adjusted the form that deals with adding images to the images table, so from now on there will be a corresponding artist_id number that matches the first_name last name entry in the artists table. Will now test that earlier join query you suggested.

 

Thanks again

[xtiancjs]

The final query I used which is giving me the results I need :

 

SELECT * FROM artists, images WHERE artists.id = images.artist_id AND ((images.caption LIKE '%$keyword%') || (images.piece_name LIKE '%$keyword%') ||

(images.show_title LIKE '%$keyword%')) GROUP BY artists.last_name

 

Thanks again mjdamato for all your help