Converting array info for html output (mysql_fetch_array)

@ad_ausJun 06.2006

Hi, I’m putting together a php code that outputs html. I got stuck on transforming results of a database query into javascript array. Any guidance will be greatly appreciated. Here are the details:

This is a part of the script that is workin now (with some help from others!):

[code=php]for ($i = 0; ($i < count($pID)); $i++) { $result=mysql_query(“select * from $table where ClientID= ‘$pID[$i]’ ;”) or die (“Unable to connect to database “);

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

$arrno1[] = $row[‘NUMBER1’]; $arrno2[] = $row[‘NUMBER2’]; $arrtx[] = $row[‘NAME’];

}

$arr1 = implode( ‘,’ , $arrno1 ); $arr2 = implode( ‘,’ , $arrno2 ); $arr3 = implode( ”,” , $arrtx );

}

echo “var a = [ $arr1 ];”; echo “var b = [ $arr2 ];”; echo “var c = [‘$arr3’];”;[/code]

The above script generates the following output in html:

var a = [ 100,158];var b = [ 20.50,40.88];var c = [‘string 1′,’string 2’];

My questions are as follows:

Is there a better way to add ‘ ‘ to individual values in the array ? If the array is empy I end up with code like this var c=[ ‘ ‘]; (I need to have elements in this array as strings otherwise the rest of my script doesn’t work…)

How to combine values from $row and some html BEFORE making them into an array? For example:

This is what prints rows of linked text in html (ie based on the values extracted from the database, iterated by the number of rows in $result):

echo “<a href=”javascript:getit(‘”.$row[Name] .”‘)”>”.$row[Description].”</a> ”;

But I want to uptut the above as individual elements in the array to print in html as follows:

var c = [ ‘<a href=”javascript:getit(‘Name1′)”>Description 1</a> ’, ‘<a href=”javascript:getit(‘Name2′)”>Description 2</a> ’]

(not the most efficient and “recommended” way of formatting html and using javascript, I know, but just for the sake of working out this example let’s assume it’s ok).

Thanks

to post a comment

PHP

6 Comments(s) _↴

@themartyJun 06.2006 — #Is there a better way to add ' ' to individual values in the array [/quote]
in what sense would you like it to be better? I don't understand what you're aiming at exactly...

How to combine values from $row and some html[/quote]
just combine the two things you already have (and escape the double quotes)

[code=php]echo "var a = ['<font face="Arial, Helvetica, sans-serif" size="-1"><a href="javascript:getit('Name1')">Description 1</a></font><br>', '<font face="Arial, Helvetica, sans-serif" size="-1"><a href="javascript:getit('Name2')">Description 2</a></font><br>'];"; [/code]

@ad_ausauthorJun 07.2006 — #Hi themarty and thanks for your interest in helping!

Ok, let's be explicit ?

re 2. Combining values for the array

My question is how to create an array of these elements:

[code=php]$arrtx[] = echo" This is".$row['Name'] ." at".$row['Description']. "; ????[/code]

so I can then do this:

[code=php]$arr3 = implode( '','' , $arrtx ); [/code]
and

[code=php]echo "var c = ['$arr3'];"; [/code]

Writing directly into html can get fiddly if the number of rows is different every time...

re 1. Printing quotes in html for array elements

If you look throught the code above, to achieve the output in html as:

var c = ['string 1','string 2'];

I am forcing an expression ',' (single quote-comma-single quote) in $arr3 = implode( '','' , $arrtx );

And then using echo to write ' (single quote) for the first and the last element in the

array: echo "var c = ['$arr3'];";

But this has the following effect if array is empty: var c=[ ' '];

I can live with this for now but I'm not sure of the future consequences for the entire

script since var c=[ ' ']; is not the same as var c=[ ];

I'm simply asking if there is an alternative way to include '' in the array (and nothing if array has no elements).

@themartyJun 07.2006 — #My question is how to create an array of these elements:

[code=php]$arrtx[] = echo" This is".$row['Name'] ." at".$row['Description']. "; ????[/code]

so I can then do this:

[code=php]$arr3 = implode( '','' , $arrtx ); [/code] [/QUOTE]

The first one should be:
[code=php] $arrtx[] = " This is ".$row['Name']." at ".$row['Description']." ";[/code]

But this has the following effect if array is empty: var c=[ ' '];

I can live with this for now but I'm not sure of the future consequences for the entire

script since var c=[ ' ']; is not the same as var c=[ ];

I'm simply asking if there is an alternative way to include '' in the array (and nothing if array has no elements).[/quote]

try this:
[code=php]echo "var c = [".($arr3=="" ? "" : "'".$arr3."'")."];";[/code]
it will only use single quotes if $arr3 is not empty

BTW,

If i indent the code from your first post a little, i must say i don't really understand it:

[code=php] // it's rather inefficient to use a count() in a for loop, as PHP will perform the count with every loop. Better to store it in a variable.
 // Besides that, a foreach-loop would be better then a for-loop btw, because now you risk accessing non-existent keys
 $totalIDs = count($pID);
 for ($i = 0; ($i < $totalIDs); $i++)
 {
 // to make debugging easier when it goes wrong, it's better to store the query in a variable
 $query = "SELECT * FROM ".$table." WHERE ClientID= '".$pID[$i]."'";
 // cuz then you can print it on the screen, together with the error, when it fails
 $result=mysql_query($query) or die ("The query:<br>n".$query."<br>nresulted in the following error: ".mysql_error());
 
  while ($row = mysql_fetch_array($result))
  {
   $arrno1[] = $row['NUMBER1'];
   $arrno2[] = $row['NUMBER2'];
   $arrtx[] = $row['NAME'];
  }
 
  $arr1 = implode( ',' , $arrno1 );
  $arr2 = implode( ',' , $arrno2 );
  $arr3 = implode( '','' , $arrtx );
 }
 
 echo "var a = [".$arr1."];";
 echo "var b = [".$arr2."];";
 echo "var c = ['".$arr3."'];";[/code]

everytime you go through the [i]for[/i]-loop you overwrite $arr1, $arr2 & $arr3. So, this code would only print the results of the last query done by the for-loop.

(And apart from that, it also seems a bit inefficient to do so many queries)

@ad_ausauthorJun 07.2006 — #Thanks themarty! Appreciate your advice.

You got me worried about the efficiency of my code... it looks simple and what it does should be simple (ie based on the list of user ID's extract relevant rows in the database, then create 3 separate arrays from selected values in each row) but I'd better review my approach!

@themartyJun 07.2006 — #this is how i would do it:

[code=php]$query = "SELECT * FROM ".$table." WHERE ClientID IN (".implode(", ", $pID).") ORDER BY ClientID";
 $result=mysql_query($query) or die ("The query:<br>n".$query."<br>nresulted in the following error: ".mysql_error());
 
 
 // initialising arrays
 $arrno1 = array();
 $arrno2 = array();
 $arrtx = array();
 
 while ($row = mysql_fetch_assoc($result))
 {
 // we're dealing with a new client
 if (!isset($oldClientID) || $oldClientID != $row['ClientID'])
 {
 /*****
 * this is not a necessary step, but in the future,
 * this is how you could detect that you're dealing
 * with a new client (or whatever it is you want to
 * distinguish in).
 * Note that in order for this to work properly,
 * You need the 'ORDER BY ClientID' in your query
 **/
 }
 
  $arrno1[] = $row['NUMBER1'];
  $arrno2[] = $row['NUMBER2'];
  $arrtx[] = $row['NAME'];
 
  $oldClientID = $row['ClientID'];
 }
 
 echo "var a = [".implode(', ', $arrno1)."];";
 echo "var b = [".implode(', ', $arrno2)."];";
 echo "var c = ['".(count($arrtx) == 0  ? "" : "'".implode('', '', $arrtx)."'")."'];";[/code]

that this is much more efficient becomes even more apparent if we remove all the comments and unnecesarry steps:

[code=php]$query = "SELECT * FROM ".$table." WHERE ClientID IN (".implode(", ", $pID).") ORDER BY ClientID";
 $result=mysql_query($query) or die ("The query:<br>n".$query."<br>nresulted in the following error: ".mysql_error());
 
 $arrno1 = array();
 $arrno2 = array();
 $arrtx = array();
 
 while ($row = mysql_fetch_assoc($result))
 {
 $arrno1[] = $row['NUMBER1'];
 $arrno2[] = $row['NUMBER2'];
 $arrtx[] = $row['NAME'];
 }
 
 echo "var a = [".implode(', ', $arrno1)."];n";
 echo "var b = [".implode(', ', $arrno2)."];n";
 echo "var c = ['".(count($arrtx) == 0  ? "" : "'".implode('', '', $arrtx)."'"))."'];n";[/code]

Now you only need to bother the database once and i also don't skipped the step where you recreate the 3 arrays in the loop.

The initializing of the arrays which i added is better, because 1) you would get an error when the error_reporting is set to E_ALL if you don't do this and 2) it's slightly faster

// edit:

You could also do this:

[code=php]$query = "SELECT * FROM ".$table." WHERE ClientID IN (".implode(", ", $pID).") ORDER BY ClientID";
 $result = mysql_query($query) or die ("The query:<br>n".$query."<br>nresulted in the following error: ".mysql_error());
 
 if (mysql_num_rows($result) == 0)
 {
 echo "var a = [];n";
 echo "var b = [];n";
 echo "var c = [];n";
 }
 else
 {
 $arrno1 = array();
 $arrno2 = array();
 $arrtx = array();
 
  while ($row = mysql_fetch_assoc($result))
  {
   $arrno1[] = $row['NUMBER1'];
   $arrno2[] = $row['NUMBER2'];
   $arrtx[] = $row['NAME'];
  }
 
  echo "var a = [".implode(', ', $arrno1)."];n";
  echo "var b = [".implode(', ', $arrno2)."];n";
  echo "var c = ['".implode('', '', $arrtx)."'")."'];n";
 }[/code]

@ad_ausauthorJun 11.2006 — #Definitely much more sensible approach since I need simplicity in the code and speed in processing the query and printing the result. Much appreciate your time in writing the solution in full!

Also in #PHP _↴

$php_self include_once for stylesheets Cycle problem

Success!

Help @ad_aus spread the word by sharing this article on Twitter...

Tweet This

Converting array info for html output (mysql_fetch_array)

6 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Converting array info for html output (mysql_fetch_array)

6 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

6 Comments(s) _↴

Also in #PHP _↴