[SOLVED] IF Statement & Two Tables With Different Echoes

[EternalSorrow]

I'm retrieving two tables in a single query using UNION ALL, like so:

 

$query = "SELECT * FROM film UNION ALL SELECT * FROM people ORDER BY id DESC LIMIT 5 ";

 

Now each of those tables, FILM and PEOPLE create links to two different pages for the different information stored in the said tables.  Here are the two different echoes for the tables:

 

FILM:

<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>

 

PEOPLE:

<li><a href="biography.php?name='.$name.'"><img src="images/thumbnails/'.$img.'.jpg" alt="" title="'.$name.' ('.$occupation.')"></a></li>

 

Is there an IF statement, or anything else for that matter, which will identify which table each row comes from and then assign the necessary link to that row, like so?:

 

if (row belongs to (FILM)) {

echo '<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>';

}

else echo '<li><a href="biography.php?name='.$name.'"><img src="images/thumbnails/'.$img.'.jpg" alt="" title="'.$name.' ('.$occupation.')"></a></li>';

 

Here's my poor attempt at managing it (obviously doesn't work):

<?php

mysql_connect(localhost,user,pw);
@mysql_select_db(db) or die( "Unable to select database");

$query = "SELECT * FROM film UNION ALL SELECT * FROM people ORDER BY id DESC LIMIT 5 ";
$result = mysql_query( $query ) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
extract($row);

$field_array = mysql_fetch_array($result);
if (!in_array(`img`, $field_array)) {

echo '<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>'; 

}
else echo '<li><a href="biography.php?name='.$name.'"><img src="images/thumbnails/'.$img.'.jpg" alt="" title="'.$name.' ('.$occupation.')"></a></li>'; 

}
?>

[bob2588]

you could use two if but not one like so

if (row belongs to (FILM)) {
echo '<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>';
}

and

if (row belongs to (People)) {
echo '<li><a href="biography.php?name='.$name.'"><img src="images/thumbnails/'.$img.'.jpg" alt="" title="'.$name.' ('.$occupation.')"></a></li>';
}

[EternalSorrow]

you could use two if but not one like so

 

If I'm using the IF statement as I've shown, with all the information passing through a single IF statement and then being either ignored to go to the ELSE or being implemented, then there shouldn't be any need for two IF statements.

 

The problem I have is not how many IF statements to use but how to define the IF statement to find the table which the row has originated.

[Philip]

I'd recommend changing your query to only pull the data you need & an identifier:

SELECT title, year, etc, 'film' as tableID FROM film UNION ALL SELECT title, year, etc, 'people' as tableID FROM people ORDER BY id DESC LIMIT 5

 

Then when calling the results: $result['tableID'] will give you the proper table.

[EternalSorrow]

Then when calling the results: $result['tableID'] will give you the proper table.

 

I understand that I now have the defining name for each of the tables, but I'm still in the dark about how to write the condition in which I can identify the table and assign the correct echo to it.  Anyone have any code snippets or links for me to learn from?

[Philip]

Using the query above....

 

if($result['tableID'] == 'film') {
echo 'film link';
}
if($result['tableID'] == 'people') {
echo 'people link';
}

[EternalSorrow]

Using the query above....

 

I pieced together what I had with what you recommended, but for some reason no results will show on the page, nor is there an error message.  When I place in a simple echo in place of the IFs the results do show.  I've tried adjusting the CONDITION using various methods, but all failed to show anything other than an error message or a blank page.

 

For reference, here's the full code (minus connection):

<?php

$query = "SELECT title, image, year, id, 'film' AS table1 FROM film UNION ALL SELECT name, img, id, occupation, 'people' AS table2 FROM people ORDER BY id DESC LIMIT 5 ";
$result = mysql_query( $query ) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
extract($row);

if ($result[table1] == 'film') {
echo '<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>';
}

if ($result['table2'] == 'people') {
echo '<li><a href="biography.php?name='.$name.'"><img src="images/thumbnails/'.$img.'.jpg" alt="" title="'.$name.' ('.$occupation.')"></a></li>';
}

}
?>

 

[Philip]

... the code above was an example using the query I had.

 

while ($row = mysql_fetch_array($result))
{
extract($row);

if (isset($table1) && $table1 == 'film') {
echo '<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>';
}

if ((isset($table2) && $table2 == 'people') {
echo '<li><a href="biography.php?name='.$name.'"><img src="images/thumbnails/'.$img.'.jpg" alt="" title="'.$name.' ('.$occupation.')"></a></li>';
}

}

[EternalSorrow]

if (isset($table1) && $table1 == 'film') {

echo '<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>';

}

 

if ((isset($table2) && $table2 == 'people') {

echo '<li><a href="biography.php?name='.$name.'"><img src="images/thumbnails/'.$img.'.jpg" alt="" title="'.$name.' ('.$occupation.')"></a></li>';

}

 

I tried using the above code without changes, but for some reason the second table in the query, regardless of which one that is, is being ignored in the results.  Thus when the FILM table is first, the films and their correct link are echoed correctly, and vice versa for the PEOPLE table.  I also tried moving around the IF statements, but that also did nothing.

[sasa]

try

<?php

$query = "SELECT title, image, year, id, 'film' AS table1 FROM film UNION ALL SELECT name, img, id, occupation, 'people' AS table2 FROM people ORDER BY id DESC LIMIT 5 ";
$result = mysql_query( $query ) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
extract($row);

if ($result[table1] == 'film') {
echo '<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>';
}

if ($result['table1'] == 'people') {
echo '<li><a href="biography.php?name='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$id.')"></a></li>';
}

[EternalSorrow]

<?php

if ($result[table1] == 'film') {
echo '<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>';
}

if ($result['table1'] == 'people') {
echo '<li><a href="biography.php?name='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$id.')"></a></li>';
}

 

When I tried that method myself (in one of my many tries) only a blank page would show.

[EternalSorrow]

Bump.  Any takers on this one?  I'm still stumped why it won't work...

[Maq]

Bump.  Any takers on this one?  I'm still stumped why it won't work...

 

Blank pages usually indicate fatal errors, put these lines directly after your opening <?php tags and post the output:

 

ini_set ("display_errors", "1");
error_reporting(E_ALL);

 

 

[EternalSorrow]

ini_set ("display_errors", "1");
error_reporting(E_ALL);

 

The notice reads:

 

Notice: Use of undefined constant table1 - assumed 'table1' in folder on line 57

 

Line 57:

if ($result[table1] == 'film') {
echo '<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>';
}

 

But whether or not the deletion of the && $table1 and && $table2 is even the most efficient (or even capable) way of handling two table echoes is still an unanswered question.

[Maq]

Place single quotes around 'table1'.  It is an associative array, as of now it thinks that table1 is a constant.

[EternalSorrow]

Place single quotes around 'table1'.  It is an associative array, as of now it thinks that table1 is a constant.

 

Even after the change the page is still empty.

[DavidAM]

Unless I am terribly mistaken (I'm at work and I don't have a system here to test with), a UNION only uses the column names from the first query.  In the subsequent query (or queries) the columns are taken in the same order as the first.  So in your query:

<?php
$query = "SELECT title, image, year, id, 'film' AS table1 FROM film UNION ALL 
SELECT name, img, id, occupation, 'people' AS table2 FROM people";

The rows returned will be named: title, img, year, id, and table1 (in that order).  When you get a row from the people table, the data from column name will be in title, data from img will be in image, id will be in year, occupation will be in id and table2 will be in table1.  Some of those pairings for columns look like they will have different datatypes; specifically, occupation and id.  This may be causing a problem on the server side; although the "or die" should be reporting the error.  You should re-arrange the second query to line up the columns better.  If you want different column names, you can use NULLs to fill columns that do not apply to one query or the other:

<?php
$query = "SELECT title, image, id, NULL AS occupation, year, 'film' AS source FROM film UNION ALL 
SELECT name, img, id, occupation, NULL, 'people' FROM people";

in this way, film.title and people.name will end up in the same column (whose name will be title). Likewise film.image and people.img will be in the same column (named image).  There will be a column named source that will identify the row's source table.  The occupation column will always be NULL (empty) for a row from the film table; and year will always be NULL for a row from the people table.

 

The ORDER BY should be affecting the entire UNION (although, as stated above, the id column may not be what you think it is in your query).  As for the LIMIT phrase, I think it applies after the ORDER BY (so it applies to the UNION'd results), but I can't test it right now.

 

I do not see why you are getting a blank page, though.  It looks like you should get something.  Can you post the code you are actually using now?

 

 

[EternalSorrow]

I do not see why you are getting a blank page, though.  It looks like you should get something.  Can you post the code you are actually using now?

 

The $id field does have a different datatype than the other fields, so there was that inconsistency there.  Through your instructions I've modified the $query statement to better reflect the two tables.

 

However, I'm currently stuck between two scripts in use.  The first of the snippets below will only show the information from the first table in the $query while the second snippet shows nothing at all:

 

First used (partially working):

<?php

$query = "SELECT title, image, id, NULL AS occupation, year, 'film' AS table1 FROM film UNION ALL SELECT name, img, id, occupation, NULL, 'people' AS table2 FROM people ORDER BY id DESC LIMIT 5";
$result = mysql_query( $query ) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
extract($row);

if (isset($table1) && $table1 == 'film') {
echo '<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>';
}

if (isset($table2) && $table2 == 'people') {
echo '<li><a href="biography.php?name='.$name.'"><img src="images/thumbnails/'.$img.'.jpg" alt="" title="'.$name.' ('.$occupation.')"></a></li>';
}

}
?>

 

Second script (blank page):

<?php

$query = "SELECT title, image, id, NULL AS occupation, year, 'film' AS table1 FROM film UNION ALL SELECT name, img, id, occupation, NULL, 'people' AS table2 FROM people ORDER BY id DESC LIMIT 5";
$result = mysql_query( $query ) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
extract($row);

if ($result['table1'] == 'film') {
echo '<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>';
}

if ($result['table1'] == 'people') {
echo '<li><a href="biography.php?name='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$id.')"></a></li>';
}

}
?>

[DavidAM]

In the first snippet, the reason that you don't get anything for the second table is because '$table2' will never be defined from the query.  Change that IF statement to test $table1, and it should work.

 

if (isset($table1) && $table1 == 'people') {
echo '<li><a href="biography.php?name='.$name.'"><img src="images/thumbnails/'.$img.'.jpg" alt="" title="'.$name.' ('.$occupation.')"></a></li>';
}

 

I do not see why the second snippet is not working. ...

 

OHH I SEE IT ...

 

if ($result['table1'] == 'film')

should actually be

if ($row['table1'] == 'film')

Same for the second IF.

 

By the way:

1) Since you are using extract(), you don't need to use $row[], you can just refer to that as $table1.

2) If you are going to use extract(), you really should use mysql_fetch_assoc() instead of mysql_fetch_array().  The mysql_fetch_array() returns an array that is keyed by column names AND by numeric indexes, so you get something like this:

array( 0 => titleValue, 1 => imageValue, 2 => idValue, 3 => occupationValue, 4 => yearValue, 5 => table1Value, 
       'title' => titleValue, 'image' => imageValue, 'id' => idValue, 'occupation' => occupationValue, 'year' => yearValue, 'table1' => table1Value)

in $row.  The numeric keys can create problems (or at least used to create problems) when using extract().

 

[EternalSorrow]

It worked :'(  It really worked :'(  I can't thank everybody near enough for the great help, and especially for teaching me so much along the way.

 

Here's the final version of the code, for future readers to view:

<?php

$query = "SELECT title, image, id, NULL AS occupation, year, 'film' AS table1 FROM film UNION ALL SELECT name, img, id, occupation, NULL, 'people' FROM people ORDER BY id DESC LIMIT 5";
$result = mysql_query( $query ) or die(mysql_error());

while ($row = mysql_fetch_assoc($result))
{
extract($row);

if ($row['table1'] == 'film')
echo '<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>';

if ($row['table1'] == 'people')
echo '<li><a href="biography.php?name='.$title.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$id.')"></a></li>';

}
?>