Re: [php-list] Database doesn't support subqueries, looking to use php to get around it

Wed, 31 May 2006 06:16:03 -0700

--- alternate_dph <[EMAIL PROTECTED]> wrote:

> I'm running a database on a server that uses a version of MySQL before
> 4.1 so I can't do subqueries.  So I tried setting the following up:
>
> $query1="SELECT DISTINCT outline_nr FROM tgs_brst_outline";
>
> $result1=mysql_query($query1) or die("Unable to view data\n");
>
> $query="SELECT tgs_saga.series_name, tgs_saga.season_nr,
> tgs_story.story_nr, tgs_story.story_name, tgs_story.story_id
> FROM tgs_story, tgs_saga, tgs_outline
> WHERE (tgs_story.saga_nr = tgs_saga.saga_nr)
> AND tgs_story.story_id NOT IN $result1
> ORDER BY tgs_saga.series_name, tgs_saga.season_nr, tgs_story.story_nr";
>
> $result=mysql_query($query) or die("Unable to view data1\n");
> #echo "<p>GOT INTO DATABASE \n</p>";
>
> I'm not quite sure why this idea doesn't work when I try running it.
> I assumed that NOT IN could work with an array so I thought the
> results of query1, being an array, would work for NOT IN.
>
> Anybody have any ideas for workarounds in php to do subqueries when
> the database doesn't support subqueries.  I hate the idea of having to
>  loop through the results of query1 to find out which story_id's
> aren't in the other table.

In your code snippet you are trying to use the result buffer as a value in the second query.  This
won't work.  You need to use one of the mysql_fetch*() functions to obtain the data.  I trust that
you have also made a connection to MySQL with the mysql_connect() function and selected a database
with the mysql_select_db() function.

In your first query you don't have any sort of ORDER BY clause so it will show up in whatever
order it happens to find in the DB.  DELETE and subsequent INSERT statements can create some
confusing results.  This may be OK depending on the second question below:

It's not clear to me whether you want to take each row found in the first query and find the rows
which do not contain the value in the second query OR if you are trying to get a lit of items in
the first query and use that as a list of items to ignore in the second query.  The IN clause
normally wants a list of values such as "field IN ('val1','val2','val3')".

As far as "NOT IN" this can be achieved as well by using the logical NOT operator (!):

! field IN ('val1','val2','val3')

If it is the latter example that you want, the easiest way to achieve it is by storing the results
of the first query in an array and then expanding the array to a list for the IN clause of the
second query:


$db = mysql_connect("localhost","dbuser","dbpassword") or die("couldn't connect to DB");
mysql_select_db("dbname") or die("couldn't select DB");

$q1 = "SELECT DISTINCT outline_nr FROM tgs_brst_outline";
$r1 = mysql_query($q1, $db) or die("$q1<hr>" . mysql_error());

# put each value found in the array called $list
while ($d1 = mysql_fetch_assoc($r1))
{
$list[] = $d1['outline_nr'];
}

# put the array back into a comma-separated string
if (is_array($list))
{
$vals = join("','", $list);
}

$q2 = "SELECT tgs_saga.series_name, tgs_saga.season_nr,
       tgs_story.story_nr, tgs_story.story_name, tgs_story.story_id
       FROM tgs_story, tgs_saga, tgs_outline
       WHERE (tgs_story.saga_nr = tgs_saga.saga_nr)
       AND ! (tgs_story.story_id IN ('$vals')
       ORDER BY tgs_saga.series_name, tgs_saga.season_nr, tgs_story.story_nr";

$r2 = mysql_query($q2, $db) or die("<pre>$q2</pre><hr>" . mysql_error());

while ($d2 = mysql_fetch_assoc($r2))
{
# do something with each result row -- in this example, display the array structure
printf("<pre>%s</pre>", print_r($d2,true));
}


This should be pretty close to what you are seeking, I believe.

James
_____


James D. Keeline
http://www.Keeline.com  http://www.Keeline.com/articles
http://Stratemeyer.org  http://www.Keeline.com/TSCollection

http://www.ITeachPHP.com -- Free Computer Classes: Linux, PHP, etc.
Spring Semester January-June 2006.  Two new class topics.


Community email addresses:
  Post message: [email protected]
  Subscribe:    [EMAIL PROTECTED]
  Unsubscribe:  [EMAIL PROTECTED]
  List owner:   [EMAIL PROTECTED]

Shortcut URL to this page:
  http://groups.yahoo.com/group/php-list



SPONSORED LINKS
Php mysql Job postings

YAHOO! GROUPS LINKS



Reply via email to