Using static table content to organize dynamic mysql content

[PixelatedKarma]

Hey everyone,

So I am having trouble getting my head wrapped around a php script I need to build. What I am trying to do is pull data from a MySQL database table (dynamic content) and match it to an html table with static content.

I guess an example of this would be having a table setup to display like so:

     | Unique ID | Info                                                                     | Etc
     |(static id)| (database searches for and populates this td based off of the static id) | (More dynamic content)

Now kinda the basis of the code you'd use regularly to display all the rows of the content in a table would be along these lines:

/* connect to the db */
$connection = mysql_connect('localhost','username','password');
mysql_select_db('my_db',$connection);

/* show tables */
$result = mysql_query('SHOW TABLES',$connection) or die('cannot show tables');
while($tableName = mysql_fetch_row($result)) {

    $table = $tableName[0];

    echo '<h3>',$table,'</h3>';
    $result2 = mysql_query('SHOW COLUMNS FROM '.$table) or die('cannot show columns from '.$table);
    if(mysql_num_rows($result2)) {
        echo '<table cellpadding="0" cellspacing="0" class="db-table">';
        echo '<tr><th>Unique ID</th><th>Info</th><th>Etc</th></tr>';
        while($row2 = mysql_fetch_row($result2)) {
            echo '<tr>';
            foreach($row2 as $key=>$value) {
                echo '<td>',$value,'</td>';
            }
            echo '</tr>';
        }
        echo '</table><br />';
    }
}

So what I'm effectively trying to do is lets say I define the row with Unique IDs as such 2013100701,2013100702,2013100703, etc. These would be hardcoded to the page as static content. IE the page would look like this:

     | Unique ID | Info                                                           | Etc
     |2013100701 | Database searches for all the entries with matching 2013100701 | (More dynamic content)
     |2013100702 | Database searches for all the entries with matching 2013100702 | (More dynamic content)
     |2013100703 | Database searches for all the entries with matching 2013100703 | (More dynamic content)

The PHP script should then use these static Unique ID's for a mysql query like so:

mysql_query (SELECT * FROM table Where unique_id = static unique id) 

This is fine and dandy but with the unique_ids statically written how do you cover that many Where clauses (and do it efficiently)? Has anyone ever attempt to do this in the past and had success?

Secondly lets say I do succeed in doing it this way, is there a way to make it so that when content is produced and it shares the same unique_id that the unique_id will actually row span for each one of those entries? I was thinking I could achieve this by putting a row_span in each of the unique_id columns a variable. Then have the script count how many of the same query are made and having that count pass to the variable thus giving it say a rowspan of 2 or 3 if there are 2 or 3 queries that return with matching unique_ids. IE:

     | Unique ID | Info                    | Etc
     |2013100701 | 2013100701 Info         | (More dynamic content)
     |           | 2013100702 Entry 1 Info | (More dynamic content)
     |2013100702 | 2013100702 Entry 2 Info | (More dynamic content)
     |           | 2013100702 Entry 3 Info | (More dynamic content)
     |2013100703 | 2013100703 Info         | (More dynamic content)

I'm so mind boggled by this that Ive just about given up and thought of throwing seperate mysql queries with if statements in each table row. This of course would take the total lines of code and multiply it by 10-20 times and that seems really taxing on the database itself....not to mention not very efficient coding if anything ever needs to be changed.

Any help, even a point in the right direction would be greatly appreciated. Thanks.

[diafol]

I'd NOT hard-write to the html table, rather store all the IDs needed in a PHP array...

$req_ids = array(2013100701,2013100702,2013100703 ... ,2013100723);

or if the ids are consecutive / sequential...

$req_ids = range(2013100701,2013100723);

The so you can insert these into SQL...

$req_ids_string = implode(",",$req_ids);

Then if you know the tables...

$tables = array("table1","table2"... "table20");

And use a foreach loop - otherwise get the table names from the SHOW TABLES sql and use a while loop. I'll use mysql_* functions here for clarity, but you may wish to use mysqli or PDO (recommended).

$output = '';
while($t = mysql_fetch_row($result)) //or foreach($tables as $tablename)
{
    $tablename = $t[0]; //if using while
    $r = mysql_query("SELECT * FROM `$tablename` WHERE id IN($req_ids_string)");
    if(mysql_num_row($r))
    {
        $head = false;
        $output .= "<table><caption>$tablename</caption>";
        while($d = mysql_fetch_assoc($r))
        {
            if(!head)
            {
                $output .= "<thead><tr>";
                foreach($d as $k=>$v)
                {
                    $output .= "<th>$k</th>";
                }
                $output .= "</tr></thead><tbody>";
                $head = true;
            }
            $output .= "<tr>";
            foreach($d as $v)
            {
                $output .= "<th>$v</th>";
            }
            $output .= "</tr>";
        }
        $output .= "</tbody></table>";
    }
}

That's off the top of the old noggin. I realise that's not exactly what you want, but it may help. I'm afraid I didn't really get it.

For the table headings, you could also use array_keys() instead of the immediate loop, but its much of a muchness.

[PixelatedKarma]

Actually thats a huge help, in terms of using an array to search the database table against it definitely has my mind going on the right track. So thanks for that, my only fear will be trying to order the array so when it is imploded it shows up in the right order but now I got a direction I can go to begin playing with it. I'll report back the results.