How to extract column info from mysql_fetch_array

ad_aus

I got totally stuck on this last bit of code for the search function I'm building ... any suggestions on how to solve this puzzle much appreciated (I'm a complete newbie, by the way!)

Ok, here is my problem:
1. I'm using an array (string of user id's) as input into sql query (sent as post from html page, "packed" with javascript, upacked in php and converted back to array which is then used to query mysql database). It works, as far as I can tell:

for ($i = 0; ($i < count($pID)); $i++) {
$result=mysql_query("select * from $table where ClientID= '$pID[$i]' ;")
or die ("Unable to connect to database ");}

As an output from the above I get an array of rows from the database (for matching user id). I need to extract a few (3) colums from that array and create a new array for each (or rather output values as a list into html).

If I use: while ($row = mysql_fetch_array($result)) {
echo "<a href=\"javascript:getit('".$row[Name] ."')\">".$row[Name] ."</a> ";
}

I can output rows of html code, not a problem. However, I can't figure out how to write to html to create a new array... for example :

var a = new Array ( - this is all html code

<? php
here goes php code to create a list a of Names, ie to ouput something like this:

one,
two,
three

where "one", "two" etc are values from "Name" column and equivalent array row in $result
note commas, if i=0 no ending comma, also no comma at the end

); - back to html code

I hope my explanation is not too confusing... Thanks!

HalfaBee

You need to create the list in the "while" loop.

while ($row = mysql_fetch_array($result)) { 
    echo "<font face=\"Arial, Helvetica, sans-serif\" size=\"-1\"><a href=\"javascript:getit('".$row[Name] ."')\">".$row[Name] ."</a></font><br>";

$names[] = $row['name'];
}
// glue together with ,
$array_list =  implode( ',' , $names );
// then echo it out

echo "var a = new Array ( $array_list )";

ad_aus

Mate, it worked! Everything is easy once you know the answer... Thanks a million for a quick and valuable reply! 🆒

I had to do some fiddling to get the output right - specific nesting of variables seems to be very important; I also had to include " " in one array - otherwise it didn't work. Here is the complete version of this part of the scriptfor the benefit of others struggling with a similar problem:

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'];";

The above script generates the following output in html:

var a = [ 100,158];var b = [ 20.50,40.88];var c = ["string 1","string2"];

If I could follow with a related question/ challenge...

Is there a better way to add " " to individual values in the array ? (I need to have them 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:

From my first post, if this is what I want to output to an array variable (ie the lot as one value):

echo "<a href=\"javascript:getit('".$row[Name] ."')\">".$row[Description]."</a> ";

and then to have it printed in html as:

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

HalfaBee

To add quotes to the strings do this

$arrno1[] = '"'.$row['NUMBER1'].'"';
$arrno2[] = '"'.$row['NUMBER2'].'"';
$arrtx[] = '"'.$row['NAME'].'"';

The same applies for html being added to strings.

ad_aus

So simple 🙂

Thanks for the trouble and great advice!