homepage Welcome to WebmasterWorld Guest from 54.197.211.197
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Joined Tables, same field name question
ManMountain



 
Msg#: 4339873 posted 8:26 am on Jul 15, 2011 (gmt 0)

Hi

probably another really basic question.

But I have a select across 2 joined tables and they both have a field STATUS, that has a different meaning in each table.

I am used to using this kind of function to access query results:

$rows = mysql_fetch_assoc($result);
$status1 = $rows['status'];

Except, the query array contains these 2 values of STATUS - how does one go about specifiying which one is which?

Thanks for your help.

Ted

 

Pico_Train

5+ Year Member



 
Msg#: 4339873 posted 9:08 am on Jul 15, 2011 (gmt 0)

select table1.status as table1_status, table2.status as table2_status

Does that help?

Status isn't the best name for a table field. I believe it is a reserved word in mysql. Try something like user_status next time or order_status. then it is already defined to you as well. You could even change the row names now and avoid the query I wrote uptop altogether.

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4339873 posted 4:15 pm on Jul 15, 2011 (gmt 0)

You can use the as keyword to assign them to a variable as mentioned, or something not many people use.

mysql_fetch_array (not assoc) returns two arrays - an associative and a list array. So you can also do

echo $row[0]; // id - remember list arrays start index at 0
echo $row[5]; first status
echo $row[11]; second status

This is extremely useful when you begin to optimize your code for expandability. A very basic example:

$table = 'mytable';
$fields = get_table_names($table); // A function you'd write
$len = count($fields);

$q = "select * from $table where id=1234";
$result = mysql_query($q) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
for ($i=0;$i<$len;$i++) {
echo "<p>Field: " . $fields[$i] . " Value: " . $row[$i] . "</p>\n";
}
}


Any table, any time . . . change the database by adding or rearranging fields, it still works. :-)

How you'd do that in a join? Also a very **basic** example,

$table = 'mytable';
$jointable = 'myjoin';
$fields = get_table_names($table);
$joinfields = get_table_names($jointable);
$len = count($fields)
$total_len = $len + count($joinfields);
$joinfield='Status';


$q = "select $table.*,$jointable.* from $table,$jointable where $table.$joinfield=$jointable.$joinfield and $table.id=1234";
$result = mysql_query($q) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
for ($i=0;$i<$total_len;$i++) {
if ($i<$len) { echo "<p>Field: " . $fields[$i]; }
else { echo "<p>Join Field: " . $joinfields[$i-$len]; }
echo " Value: " . $row[$i] . "</p>\n";
}
}


You can also use array_keys to get field names, but sometimes you need them before actually running a query (table heads.)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved