Topic: Quickie - returning specific info from a Select * request

[spenceddd]

Hello,

Could anyone please show me how to access specific items from the array returned from this request:

Code: [Select]

//Request all info from table
$query = "SELECT * FROM PortfolioItems ";   
$wholePortfolioArray = mysql_query($query) or die(mysql_error());

...where the table looks like:

ID MN OX DD
01 YY NN XX
02 YY MM XX
03 NN MM XX
04 NN MN XO

For instance I would like to return just the 'ID' and 'OX' colomns of each row.

I would also like to know how to return the value at ID = 03, colomn 'MN'.

The reason I am doing it like this is because I only want to make the request to the server before the page loads. After that I want to use javascript to sort through the information.

Maybe there is a way to convert the entire array to a javascript array for client side access...?

Thanks very mcuh for any help.

Spencer

[KevinM1]

SELECT ID, OX FROM PortfolioItems

SELECT can do far more than merely return everything.

[thewooleymammoth]

Code: [Select]

<?php
/*how to echo each returned row :: the rest is up to you to figure out */
$ar = mysql_query($query);
while($info = mysql_fetch_assoc($ar))
{
  foreach($info as $key=>$i)
  {
     echo "$key - $i ::";
  }
     echo "<br />
    ";
     
/*to echo a specific column*/
     echo $info['id'];
}
?>

This should echo out each key and data for each row. mysql_fetch_assoc() returns the first row the first time you call it and the 2nd row the second time etc...


my code has not been tested

The reason I am doing it like this is because I only want to make the request to the server before the page loads. After that I want to use javascript to sort through the information.

Maybe there is a way to convert the entire array to a javascript array for client side access...?

Php only works before the page loads. you could use php to populate the javascript array

Code: [Select]

<?php
/*how to echo each returned row :: the rest is up to you to figure out */
$ar = mysql_query($query);
$row=1;
while($info = mysql_fetch_assoc($ar))
{
  
      echo "<script type='text/javascript>
          var ar$row = new array();
       ";
  foreach($info as $key=>$i)
  {
     echo "ar.$key = $i
";
  }
 $row++;
}
?>

my js is a little rusty so you might have to correct mistakes but you get the picture.

Have fun exploring.

[wildteen88]

If you're populating a javascript array with PHP code. Then PHP has a handy function called json_encode() which will do all the hard work for you.

[spenceddd]

Great! Thanks for the tips guys they are a big big help!!

Now I've just got to work out json_encode()

It's very interseting also to see how you write the JS into the php loop.

[spenceddd]

Okay I have used the json object here (I think succesfully as it prints ok):

Code: [Select]


//Request all info from database
$query = "SELECT * FROM PortfolioItems ";   
$wholePortfolioArray = mysql_query($query) or die(mysql_error());
   // iterate over every row
    while ($row = mysql_fetch_assoc($wholePortfolioArray)) {
        // for every field in the result..
        for ($i=0; $i < mysql_num_fields($wholePortfolioArray); $i++) {
            $info = mysql_fetch_field($wholePortfolioArray, $i);
            $type = $info->type;

            // cast for real
            if ($type == 'real')
                $row[$info->name] = doubleval($row[$info->name]);
            // cast for int
            if ($type == 'int')
                $row[$info->name] = intval($row[$info->name]);
        }

        $rows[] = $row;
    }

    // JSON-ify all rows together as one big array
    echo json_encode($rows);
   
   

Now I am trying to recall the info in javascript.

Could anyone help me print the array in javascript?

Thanks again for any help on this.

Spence

[thewooleymammoth]

If you're populating a javascript array with PHP code. Then PHP has a handy function called json_encode() which will do all the hard work for you.

you would think with all the php i do i would have discovered that long ago.... Learn somethin new everyday.

Now I am trying to recall the info in javascript.

Could anyone help me print the array in javascript?

Thanks again for any help on this.

Spence

You might want to check out the w3schools tutorial on js loops. Here is a link for the for each equivalent in js

http://pietschsoft.com/post/2008/02/28/JavaScript-ForEach-Equivalent.aspx

havnt tested it though

[spenceddd]

Great! Ill check it out thanks.