Can I loop a sql query???

[userxtian]

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 recordset 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 recordset 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 ? Using php/mysql

[Günter_Schenk]

well, I´ve been thinking about that, and the only idea I had was using a subquery for this, like:

<?php
$query_subsearch="SELECT CONCAT(firstname, ' ', lastname) AS fullname FROM other_table WHERE lastname LIKE '".%$search['artist']%."'";
$subsearch = mysql_query($query_subsearch, $nancy) or die(mysql_error());
$row_subsearch = mysql_fetch_assoc($search);
$totalRows_subsearch = mysql_num_rows($subsearch);
?>
<?php do { ?>
- <?php echo $row_subsearch['fullname']; ?>
<br>
<?php } while ($row_subsearch = mysql_fetch_assoc($subsearch)); ?>
<?php
mysql_free_result($subsearch);
?>

This example might or might not work, but introducing subqueries as such is possible -- there might be other options which don´t require matching against another table at all, but please let me know, if...

1. you need that for displaying purposes alone ?

2. the "format" of the names is always identical both in the artist field (e.g. JohnDoe) and the other table (John Doe) -- in particular: will the "last name" always start with an uppercase letter ?

If yes, I´ve been thinking about applying a case sensitive regular expression on the "artist" field alone which will...

a) detect the last lower case letter that´s preceeding the following Upper case letter and capture each element into a backreference (see the $pattern variable), and

b) add a hardcoded space in between those backreferences (see the $replacement variable).

If this approach should match the 2nd. "last name always starts with an Upper Case letter" requirement, give it a try using the following example

----------------
<?php
$test = "JohnDoe";
$pattern = '/([a-z])([A-Z])/';
$replacement = '${1} ${2}';
$test = preg_replace($pattern,$replacement,$test);
?>

<?php
echo $test;
?>
-------------------

if not, I actually don´t have any other idea at the moment 😉

[userxtian · Accepted solution]

Hi Geschenk,
After looking around at a few different forums here is how I solved the problem.
via the terminal I created a new column in my images table called 'artist_id" , I populated this column with the corresponding id from the artists table :

UPDATE images, artists SET images.artist_id = artists.id WHERE CONCAT(artists.first_name, artists.last_name) = images.artist

I then adjusted the form which inserts a new image to also add the correct artist_id , for new images that are added.
My final SELECT statement 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

Yeah the surname always begins with a cap. Will try out that pattern matching etc code you suggested , it could be useful for a later application

Thanks for taking the time to look at my coding issue.

[Günter_Schenk]

Hi,

congrats and hats off to this brilliant solution 😉