Can I loop a sql query???

Question

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

userxtian · Accepted Answer

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 · Answer

well, I´ve been thinking about that, and the only idea Ihad was using a subquery for this, like:-
This example might or might not work, but introducingsubqueries as such is possible -- there might be other optionswhich don´t require matching against another table at all, butplease let me know, if...1. you need that for displaying purposes alone ?2. the "format" of the names is always identical both in theartist field (e.g. JohnDoe) and the other table (John Doe) -- in particular: will the "last name" alwaysstart with an uppercase letter ?If yes, I´ve been thinking about applying a casesensitive regular expression on the "artist" field alone whichwill...a) detect the last lower case letter that´s preceedingthe following Upper case letter and capture each element into abackreference (see the $pattern variable), andb) add a hardcoded space in between those backreferences (see the $replacement variable).If this approach should match the 2nd. "last name alwaysstarts with an Upper Case letter" requirement, give it a try usingthe following example-----------------------------------if not, I actually don´t have any other idea at themoment ;-)

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.