[RESOLVED] Easier way to get row values?

conjurer

I am running a query on MySQL table...the query works, on line 18 in the code below I start a while loop with

while ($row = mysqli_fetch_assoc($result))

I then run some code to make alternate styles of <tr class="odd"> or <tr class="even"> so I can apply some CSS styles to the table to show shading in background.

Then I have the row where I out put the fetched results on line 26-27. I was surprised that when I used the $row array descriptor in an echo command with double quotes it doesn't interpret the value... If written like this:

echo "<td>$row['user_id']</td><td>$row['first_name'] $row['last_name']</td>";

The line fails...

But if I break the variables out of the double quotes and add a dot to concatenate like this:

echo "<td>".$row['user_id']."</td><td>".$row['first_name'] ." ".$row['last_name']."</td>";

Then it works.

I thought variables were interpreted inside double quotes so why does this not work here?

And is there an easier way to output these results such that I can wrap them with a html td tag in the process? Right now I am only using a couple columns to test but soon that query will be expanded to about 15 fields I want to put into a table.

Any suggestions on a better way to write it than in line 26-27?

 1  if (!$_SESSION['valid_user'])
 2  {
 3    echo "<h2>You are not logged in to run query!</h2>";
 4    exit;
 5    }
 6    else
 7   {
 8    $query = "select user_id, last_name, first_name from directory ";
 9    echo "<p>Running query for ".$_SESSION['valid_user'] .":<br /> $query </p>";
10   }
11    $result = mysqli_query($conn, $query);
12    $num_results = mysqli_num_rows($result);
13    echo "<p>Number of rows: $num_results<br>Returning results:</p>";
14    $style="odd";
15    echo "<table>\n";
16    echo "<tr><th>Id</th><th>Name</th></tr>\n";
17
18    while ($row = mysqli_fetch_assoc($result))
19   {
20      if ($style=="odd") //set rows as even and odd
21        $style = "even";
22        else
23        $style = "odd"; 
24      echo "<tr class=\"$style\">";
25    

26   	echo "<td>".$row['user_id']."</td><td>".$row['first_name']
27              ." ".$row['last_name']."</td>";
28     echo "</tr>\n";
29    }
30    echo "</table>\n";

NogDog

The parser gets confused when you use an associative array element variable in a quoted string. You can help it tell where the variable name begins and ends by using "complex variable notation" (curly braces):

echo "This is a test of {$complex['variable']} notation.\n";

esukf

Try

echo "<td>{$row['user_id']}</td><td>{$row['first_name']} {$row['last_name']}</td>";

conjurer

Cool - Thanks!

NogDog

Alternatively, you could sprintf() or printf():

printf("<td>%s</td><td>%s %s</td>", 
       $row['user_id'], 
       $row['first_name'], 
       $row['last_name']);

conjurer

Oooh - very elegant!

conjurer

That is elegant, but I need a way to set up a list of labels to head the table and since column names are usually not very elegant (unless I redefine them in the query using select x as y) I decided a better way might be to just define an array of the headings I want and then using your earlier help NogDog I came up with this:

$query = "select user_id,(CONCAT(first_name,\" \",last_name))as Name from directory order by last_name, first_name ";
    $t_labels= array("ID","Name");  //set array for table lables
    echo "<p>Running query for ".$_SESSION['valid_user'] .":<br /> $query </p>";



function do_query($conn, $query, $t_labels)
{
  $result = mysqli_query($conn, $query);
  $num_results = mysqli_num_rows($result);

  echo "<p>Number of rows found: $num_results<br>Returning results:</p>";
  $style="odd";
  $heading=FALSE;

  while ($row = mysqli_fetch_assoc($result))
  {
    if (!$heading) //only do if header row hasn't been output yet
    {
      $heading = TRUE; //so we only do this once
        echo "<table>\n<tr>\n";
        foreach ($t_labels as $val)
          {
            echo "<th>$val</th>";
          }
          echo "</tr>\n";
     }	


    if ($style=="odd") //set rows as even and odd
        $style = "even";
      else
        $style = "odd"; 

    echo "<tr class=\"$style\">"; 

      foreach($row as $val)
      { 
        echo "<td>$val</td>";
      }
    echo "</tr>\n";


  }//close while
echo "</table>\n"; 
 }//close function  


 do_query($conn, $query, $t_labels);

That is working nicely. I may have to think about the use of select AS though, then I wouldn't need to do the array and could just use the keys the way you showed me before:

foreach ($row as key => $val)
{
  echo "<th>$key</th>"
}

Anyway - it is coming together thanks to all your help!

conjurer

NogDog's printf solution got me playing with the code and now it is broken!

I was thinking I could do a "Select * from table" and then use the printf statement to specify which columns to print. But what I tried below duplicates the fields across the screen for about 16 times before it ends the row.

I assume I am screwed up on the foreach command but don't really now how to get it to move through the rows.

	function do_query($conn, $query)
	{
 	  $result = mysqli_query($conn, $query);
	  $style="odd";
	  $heading=FALSE;

  while ($row = mysqli_fetch_assoc($result))
  {
    if (!$heading) //only do if header row hasn't been output yet
    {
      $heading = TRUE; //so we only do this once
        echo "<table>\n<tr>\n";
        foreach ($row as $key =>$val)
          {
          printf("<td>%s</td><td>%s </td>", "ID","Name");

          }
          echo "</tr>\n";
     }	


    if ($style=="odd") //set rows as even and odd
        $style = "even";
      else
        $style = "odd"; 

    echo "<tr class=\"$style\">"; 

      foreach($row as $val)
      { 
        printf("<td>%s</td><td>%s %s</td>", 
             $row['user_id'], 
             $row['first_name'], 
             $row['last_name']);
      }
    echo "</tr>\n";


  }//close while
echo "</table>\n"; 
 }

Any thoughts on how best to do this?