MySQL statement that behaves similarly to a "foreach" or "while"

[DJRobThaMan]

Hi everyone,

I had a quick question about a MySQL query I need to implement. What I want to do is to select everything in a table for the most recent date for each pid.

I have tried the following:

select * from versionData where date=(select max(date) where pid=pid)

And clearly it doesn't work.

What I'd like to do in one staement (if that is at all possible) is to combine the following:

SQL Statement 1: select distinct(pid) from versionData

Then for each pid in the table

SQL Statement 2,3,4...: select * from (select * from versionData where pid=[PID1, PID2...]) as stuff where date=(select max(date) from versionData where pid=[PID1, PID2...])

Anybody know if this is possible?

Thanks

[NogDog]

I think maybe you just need to do a "join", but I'm not completely certain without a better understanding of the tables involved and their relationships.

[DJRobThaMan]

Okay,

First, I haven't tested this thoroughly so it might still not be right, but I have implemented something with a mix of mysql statements and php to get it done.


$searchquery = mysql_query("select distinct(pid) from " . $db . " where " . $item . "");
$searchtemp = array();

while($searchans = mysql_fetch_array($seachquery))
{
$searchtemp[count($searchtemp)] = $searchans['pid'];
}

$i = 0;

while($i < count($searchtemp))
{
$searchquery = mysql_query("select * from " . $db . " where (" . $item . ") and pid='" . $searchtemp[$i] . "' and date=(select max(date) from " . $db . " where pid='" . $searchtemp[$i] . "')");
$search[$i] = mysql_fetch_array($searchquery);
$i++;
}


Second, let me see if I can give you the gist of the db structure.

projectData
pid (primary key)
pname
author
description
tid

versionData
pid
vid
pvid (primary key)
data
date
published

Those are the two tables being selected from here. All that's happening is that people are posting various projects and the details are being stored in the db. Here's a little description of what all these field signify.

pid - Project ID (an auto incremented integer)
vid - Version ID (could be anything)
pvid - An ID for each version of data uploaded (an auto incrememented integer)
pname - Project name
author - Author name
tid - Type ID (this ties back with another table that holds the info about which type is associated with which type ID)
published - a 0 or 1 which is to set whether or not the project can be seen via the web site

I hope that both what I have already implemented and the quick description is clear. I am still searching for that all-encompassing MySQL statement. I don't really want to hit the db with so many requests.

Again, thanks for taking a look.

[NogDog]

So it sounds to me like you want something along the lines of

<?php
$sql = "
SELECT *, MAX(vd.date)
FROM versionData AS vd
INNER JOIN projectData AS pd ON vd.pid = pd.pid
WHERE vd.published = 1
GROUP BY pd.pid
";

Since I don't know what some of those variables represent in your WHERE clauses, I suspect you may need to add some other conditions to this WHERE clause.

[DJRobThaMan]

Ok. I've given up hope on an all-in-one statement that's going to do what I want it to. But I think I've come up with a marginally better solution here.


if($_GET['data'] == "advanced")
{
$db = '';
$item = '';

if(isset($_GET['author']) || isset($_GET['pname']))
{
$db = 'projectData';

if(isset($_GET['author']))
{
$item = 'author like \'%' . $_GET['author'] . '%\'';
}

if(isset($_GET['pname']))
{
$item .= '&pname like \'%' . $_GET['pname'] . '%\'';
}

if(strpos($item, '&') !== false)
{
if(strpos($item, '&') > 0)
{
strtr($item, '&', ' or ');
}
else
{
strtr($item, '&', '');
}
}
}

if(isset($_GET['version']))
{
$db .= '&versionData';
}

if(strpos($db, '&') !== false)
{
if(strpos($db, '&') > 0)
{
strtr($db, '&', ' natural join ');
}
else
{
strtr($db, '&', '');
}
}

$searchquery = mysql_query("select pid, max(date) as mdate from " . $db . " where " . $item . " group by pid");

while($search = mysql_fetch_array($seachquery))
{
$searchsub = mysql_query("select * from projectData natural join versionData where pid='" . $search['pid'] . "' and date='" . $search['mdate'] . "'");
$ans = mysql_fetch_array($searchsub);
$return .= '<div class="depbox" id="depsug' . $ans['pvid'] . '"><div class="left"><h3>' . $ans['pname'] . '</h3><small>Latest Version:' . $ans['mvid'] . '</small><p>' . $ans[$i]['description'] . '</p></div><div class="right"><a title="Mark this project as a dependency" href="#">+</a></div></div>';
}

}
else
{
$searchreq = mysql_query("select pid, max(date) from projectData natural join versionData where pname like '%" . $_GET['search'] . "%' or author like '%" . $_GET['search'] . "%' group by pid");
$searchtemp = array();

while($searchans = mysql_fetch_array($searchreq))
{
$searchsub = mysql_query("select * from projectData natural join versionData where pid='" . $search['pid'] . "' and date='" . $search['mdate'] . "'");
$ans = mysql_fetch_array($searchsub);
$return .= '<div class="depbox" id="depsug' . $ans['pvid'] . '"><div class="left"><h3>' . $ans['pname'] . '</h3><small>Latest Version:' . $ans['mvid'] . '</small><p>' . $ans[$i]['description'] . '</p></div><div class="right"><a title="Mark this project as a dependency" href="#">+</a></div></div>';
}
}