basic php/mysql syntax question on a single line of code

[cunoodle2]

What is the proper syntax on this line here...

<?php	$id = $row["Detail.id"];?>

 

I know it has something to do with the "Detail.id" portion as I can get it to work on all portions without the "." in the syntax.  The problem is that the above is showing nothing when I output it.  Here is the full code leading up to it..

<?php	$sql = "SELECT Detail.id, Detail.up_test, Detail.count_test, Detail.name, Detail.server, Detail.port, Detail.result, server.id, server.ip, server.name FROM `Detail`, `server` WHERE Detail.server = server.id;";

if ($query_result = mysql_query($sql))
{
	while ($row = mysql_fetch_array($query_result))
	{
		$id = $row["Detail.id"];

		echo "ID: ".$id."<br />\n";
?>

 

It should output something but it shows nothing.  If I run that long SQL on the machine via SSH or via phpmyadmin I get a full set of results without error.

[Q695]

<?php   $id = $row[id];?>

[Q695]

this should work:

<?php   $sql = "SELECT * FROM `Detail`, `server` WHERE Detail.server = server.id;";

   if ($query_result = mysql_query($sql))
   {
      while ($row = mysql_fetch_array($query_result))
      {
         $id = $row[id];

         echo "ID: ".$id."<br />\n";
?>

[cunoodle2]

<?php   $id = $row[id];?>

This would not work in the case I have listed as I'm using a JOIN statement and I have two "id" fields (Detail.id and server.id).  Based upon your syntax I would have no way to differentiate between the two unless I use "AS" variable assigners in the original SQL statement.

 

For example if I do this...

<?php
$sql = "SELECT Detail.id AS det_id,  server.id AS serv_id FROM `Detail`, `server` WHERE Detail.server = server.id;";
?>

 

THEN I can do the following and it works fine...

<?php
echo $row[det_id];
echo $row[serv_id];
?>

 

The above would work fine.  It has to do with the "." in the statement.  I tried using the escape character ("/") but that did NOT do the trick.  Any other ideas?

[cunoodle2]

I'm sure someone is going to just suggest altering the statement to include AS assigners but I'd rather not go through all the rest of my code and change things.  Additionally I'm just interested in learning the proper way to do this (if its even possible without using the AS assigner in the original SQL statement.

[cunoodle2]

Does anyone have ides on this?  I have tried all aspects of everything but still cannot determine the proper syntax in my original post

[premiso]

Run a print_r on the $row and see how it is being returned. Given that it should give you the proper array index name.

[Q695]

presmiso don't you mean to use a modified version of:

http://www.phpfreaks.com/forums/index.php/topic,238332.msg1110284.html#msg1110284

[premiso]

presmiso don't you mean to use a modified version of:

http://www.phpfreaks.com/forums/index.php/topic,238332.msg1110284.html#msg1110284

 

No...a print_r will give him the array indexes names and values just fine. It will work for him to "figure" out how mysql returns his data so he can use the proper array index value.

 

This is a simple 1-time deal for him to see how it is being returned, it is not meant to be kept in there.

 

EDIT:

And your post was asking for something specific, this is just finding out what index is being returned from a query with multiple tables.

[cunoodle2]

Run a print_r on the $row and see how it is being returned. Given that it should give you the proper array index name.

I appreciate the suggestion and I honestly thought this was going to do the trick but the results are quite strange.  Here is the code I used...

<?	$sql = "SELECT Detail.id, Detail.up_test, Detail.count_test, Detail.name, Detail.server, Detail.port, Detail.result, server.id, server.ip, server.name FROM `Detail`, `server` WHERE Detail.server = server.id;";

if ($query_result = mysql_query($sql))
{
	while ($row = mysql_fetch_array($query_result))
		{
         		print_r($row);
		echo "<br />\n";
		}
}
?>

 

Here are the results of this.  One line at at time....

Array ( 
[0] => 1 [id] => 10 
[1] => 771 [up_test] => 771 
[2] => 774 [count_test] => 774 
[3] => http [name] => server22 
[4] => 10 [server] => 10 
[5] => 80 [port] => 80 
[6] => 1 [result] => 1 
[7] => 10 
[8] => 127.0.0.1 [ip] => 127.0.0.1 
[9] => server22
)

 

What is very strange is according to the array layout element "[7]" which realistically is "server.id" cannot be called via any other access method other than referencing the '7' position of the array.  The 7 represents the 8th item (it starts at 0) and if you look at the query the 8th item listed in the orginal array is also "server.id"

 

Another strange occurrence is that of element "[3]."  That line SHOULD read "[3] => http [name] => http" but according to the array print out is actually displaying item "Detail.name" and "server.name" (in that order) when it should in fact be repeating the item twice on the same line as it did in all other examples.

 

So back to my original question.  Does anyone know the correct syntax for the following line of code...

<?php   $id = $row["Detail.id"];?>

 

Is it even possible to do?  Or am I required to use "AS" assigners to change the name of the sql variable (see my 3rd post in this topic at 01:57:11 AM for a further explain).

 

[premiso]

That is interesting. Basically it overwrites the id. This is why I use unique ids for tables, like server table it would serid and detail would be detid as the column name. Avoids this and other issues.

 

To avoid this use as, but since you are using the mysql_fetch_array you can call it by the index listed at. So for the serverid it would be $row[7] or $row['id'] since that is last in the list it overwrites the first and the detail id would be accessed by $row[0].

 

EDIT:

I generally name my columns in table that way (sername detname) because of such issues with joins and that way I know which table I am pulling from. Because of this and since you actually list the columns I would just use mysql_fetch_row and access the data by their indexes,

[Philip]

Why not in your query use AS

SELECT Detail.id as dID, Detail.up_test, Detail.count_test, Detail.name, Detail.server, Detail.port, Detail.result, server.id as sID, server.ip, server.name FROM `Detail`, `server` WHERE Detail.server = sID;

 

later on call it as:

$row['dID'] or $row['sID']

 

edit: just read the other post about trying to not use as. Unless you did it via the numeric index (fetch_array) then no.

[samshel]

have you tried mysql_fetch_assoc(); ? may not make any difference, but it will return u the key even if it overwrites...just a guess.

[cunoodle2]

I'm fully aware that I can use either the numeric key and/or use "AS" to change variable names.  My question specifically is wondering if it's possible to do without using either of the two listed items here.  Is there any way to do..

 

echo $row['table.column"];  ?????

[Q695]

I don't use quotes in arrays, but you can, if you feel like you need to use it that way.

[Philip]

I don't use quotes in arrays, but you can, if you feel like you need to use it that way.

 

That's bad practice. It will throw a notice about that, because it's trying to look for a defined constant. Example:

<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);
define('hi', 'hello there!');
$a = array('a'=>'b', 'hi'=>'hello');
echo $a[hi]; // wrong!
?>

Notice: Undefined index: hello there! in ...script.php on line 6

and...

<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);
$a = array('a'=>'b', 'hi'=>'hello');
echo $a[hi]; // wrong!
?>

Notice: Use of undefined constant hi - assumed 'hi' in ...script.php on line 5

hello

Where, when correctly coded:

<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);
$a = array('a'=>'b', 'hi'=>'hello');
echo $a['hi']; // correct!
?>

hello

 

---

 

@OP: Unfortunately, I don't think there is any other way.

[Q695]

Why does it work with $row then?

[premiso]

Why does it work with $row then?

 

It works for any array if you do not have a constant set with that name. Because if PHP does not find it as a constant it throws an error and interprets the "value" literally.

 

It is bad practice not to surround non-numeric indexes of arrays in quotes (single or double). Due to the Undefined constant error. It is not a huge error, but if you code it right it is more efficient, since it does not have to throw that error and search if a constant is defined with that etc. With the single/double quotes it does not take that extra time searching and just does.

 

I'm fully aware that I can use either the numeric key and/or use "AS" to change variable names.  My question specifically is wondering if it's possible to do without using either of the two listed items here.  Is there any way to do..

 

The only options you are are "As", the numerical index, or re-designing your table column names as I suggested above. Simple as that.