put all records into an dynamic array

[lleemon]

I am a newbie to PHP and MySQL. I currently do all my stuff in ASP and would like to learn the power of PHP. One of the things I am wondering is if you can do like asp and put all the records into an array so you would loop through the array not each record. I see the function mysql_fetch_array does fetch one row at a time but can I do a dynamic array like this?

Also please fill me in if PHP doesn't really matter if you hit each record since it may be more efficient at this. I don't have a clue. Someone please educate me on this.

Thanks in advance for your help.

[The Hobo]

mysql_fetch_array() would be the best method. Adding them to an array and then accessing them would just be overkill.

[lleemon]

Thanks for the response. But does do I have to loop through all of the records, one by one, to put them into an dynamic array? I know I can put one record into an array. With one command like the ADO .GETROWS function I can declare a variable and with this function it puts all the returned rows into a dynamic array.

True or False. If I have to loop through all the returned record sets this saves me little processing between the database and php?

I believe the .Getrows saves big time because it's only accessing the database one time. Not for each record.

Hope I hear from you.

[phpman]

Originally posted by chrisjk
mysql_fetch_array does return an array

$result = mysql_query("select * from table");

do while ($rows = mysql_fetch_array($result)) {
    echo $rows[fieldname];
} 

$rows is an array containing that record accessed by giving it the fieldname

you don't need the "do" in there.

$result = mysql_query("select * from table");

while ($rows = mysql_fetch_array($result)) {
    echo $rows[fieldname];
} 

will output eveything it finds in the table, if the table only has one row it will output one row, if it has 50 rows then it will output 50 rows.

[lleemon]

Okay. I am finding out that I have now clue about php.

The following suggestions do work but not like I want them to. For some reason I can only loop one time. Yes, I know about the reset function but I keep getting an error saying something about this isn't an array.

Here is what I am trying to do:
I have a table with say 3 fields: ID, Model, Brand
Here is how it might look in the database.
ID - Model - Brand
1 - M1 - Rocka
2 - M2 - Rocka
3 - M3 - Rocka
4 - G43 - Blackbo

I have a .html page with checkboxes and when they select one to four items say 1, 3, and 4 I want the output to be like

Model - M1 - M2 - G43
Brand - Rocka - Rocka- Blackbo

I orginally though I could reset the array created from the mysql_fetch function but it didn't work.

Anyone have any suggestions on how I may do this?

I must say to all you php guru's that the vb .getrows is much simpler and easier to work with.

Thanks.

[phpman]

php is a ot easier if you know how to do it.

you have to put what you want to find in the query not reset teh array. that will do nothing.

what is the code you have now.

[lleemon]

phpman,
Should it matter what the query is? I guess my goal is to be able to take the results of any query that is created dynamically and put into a array.

Once I have an array I should be able to loop through it as many times as I want.

I have done all of this using vbscript but I am having diffulculty with php's arrays.

My current code which does not work at all:

<?php
$MainSql = "WHERE ";
error_reporting(E_ALL);
if(isset($_GET) && isset($_GET["chkCompare"])){
    $arIncoming = $_GET["chkCompare"];
    foreach($arIncoming as $k => $v) { 
        if ($k == 0) {
            $arBrandModel = split("_", $v);
            //echo '<br>element['.$k.'] = '.$v;
            $MainSql = $MainSql . "(Brand = '" . $arBrandModel[0] . "' AND Model = '" . $arBrandModel[1] . "') ";
        } else {
            $arBrandModel = split("_", $v);
            //echo '<br>element['.$k.'] = '.$v;
            $MainSql = $MainSql . "OR (Brand = '" . $arBrandModel[0] . "' AND Model = '" . $arBrandModel[1] . "') ";
        }
    }
}

mysql_select_db($database_com, $vm_com);
$query_rsVacs = "SELECT * FROM vm_php " . $MainSql;
$rsVacs = mysql_query($query_rsVacs, $vm_com) or die(mysql_error());
$row_rsVacs = mysql_fetch_assoc($rsVacs);
$totalRows_rsVacs = mysql_num_rows($rsVacs);
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php
$link = mysql_connect(vm.com, 'user', 'pwd');

$fields = mysql_list_fields("vm_com", "vm_php", $link);
$columns = mysql_num_fields($fields);
?>
    <table>
<?php
    for ($i = 0; $i < $columns; $i++) {
?>
    <tr>
<?php    
        echo "<td>" . mysql_field_name($fields, $i) . "</td>";
        do { 
        echo $row_rsVacs[mysql_field_name($fields, $i)] . "||";
 } while ($row_rsVacs = mysql_fetch_assoc($rsVacs)); 

?>
    </tr>
<?php
    }
?>
</table>


</body>
</html>
<?php

mysql_free_result($rsVacs);
?>

[phpman]

phpman,
Should it matter what the query is?

well, YES it does matter. if you want
I have a .html page with checkboxes and when they select one to four items say 1, 3, and 4 I want the output to be like
then the query is your best friend. make the db work for you instead of the code.

speaking of code, what the hell is all that crap??? you connect twice when you didn't need to and you used stuff you have no need to use. for somebody that doesn't understand php code you sure used some advanced coding. for one you can't do a query if you never connect to the db. you selected then you never made a connection. I am only taking a guess here as you didn't say anything about the form you have or what variables you have for them or wht errors you are getting. so this is an idea and should get you going.

<?php
$link = mysql_connect(vm.com, 'user', 'pwd');
mysql_select_db($database_com);


$MainSql = "WHERE ";
error_reporting(E_ALL);
if(isset($_GET) && isset($_GET["chkCompare"])){
    $arIncoming = $_GET["chkCompare"];
    foreach($arIncoming as $k => $v) { 
        if ($k == 0) {
            $arBrandModel = split("_", $v);
            //echo '<br>element['.$k.'] = '.$v;
            $MainSql = $MainSql . "(Brand = '" . $arBrandModel[0] . "' AND Model = '" . $arBrandModel[1] . "') ";
        } else {
            $arBrandModel = split("_", $v);
            //echo '<br>element['.$k.'] = '.$v;
            $MainSql = $MainSql . "OR (Brand = '" . $arBrandModel[0] . "' AND Model = '" . $arBrandModel[1] . "') ";
        }
    }
}


$query_rsVacs = "SELECT * FROM vm_php " . $MainSql;
$rsVacs = mysql_query($query_rsVacs, $vm_com) or die(mysql_error());
$row_rsVacs = mysql_fetch_assoc($rsVacs);
$totalRows_rsVacs = mysql_num_rows($rsVacs);
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php
$fields = mysql_list_fields("vm_com", "vm_php", $link);
$columns = mysql_num_fields($fields);
?>
    <table>
<?php
    for ($i = 0; $i < $columns; $i++) {
    echo"<tr>";

        echo "<td>" . mysql_field_name($fields, $i) . "</td>";
        do { 
        echo $row_rsVacs[mysql_field_name($fields, $i)] . "||";
 } while ($row_rsVacs = mysql_fetch_assoc($rsVacs)); 


   echo" </tr>";
    }

echo"</table>";


</body>
</html>
<?php

mysql_free_result($rsVacs);
?>

[lleemon]

phpman,
Sorry to get you stirred up. I guess I didn't share the whole story.

1. I have a front end page that is dynamic and reads through each item in the database and creates a checkbox with the name of chkCompare. The value is then given the brand name and model name with a underscore seperating both. Exp. value=Brand_Model

2. The code I posted is not all of my code. I created this page with Dreamweaver MX. If you are familiar with this tool you will know it automatically creates a connection file and then puts a include at the top of the page that loads are needed connection variables. I didn't feel this was needed since I just wanted to discuss the core issue.

Maybe I am asking the wrong question. I guess I just need to find out how to populate a multidimensional array that can be looped through an many times. The current code only seems to loop through one time and ends. If someone can give me a some solid code or a good url.

Thanks for you help.

[phpman]

can we see your form? the brand_model is a bad idea. too messy. lets see the code for the form adn I will whip something up.

[lleemon]

Here is all the code from my first form. It just is a plain page the displays Brand, Model and checkbox for each item in db. This code is generated by Dreamweaver MX.

<?php require_once('../Connections/vm_com.php'); ?>
<?php
mysql_select_db($database_vm_com, $vm_com);
$query_rsBrandModel = "SELECT * FROM vm_php";
$rsBrandModel = mysql_query($query_rsBrandModel, $vm_com) or die(mysql_error());
$row_rsBrandModel = mysql_fetch_assoc($rsBrandModel);
$totalRows_rsBrandModel = mysql_num_rows($rsBrandModel);
?>
<html>
<head>
<title>Testing Array of checkboxes</title>
</head>
<body>
<form method="GET" action="comparetest.php">
  <table width="385" border="0">
    <?php do { ?>
    <tr> 
      <td><?php echo $row_rsBrandModel['Brand']; ?></td>
      <td><?php echo $row_rsBrandModel['Model']; ?></td>
      <td><input name="chkCompare[]" type="checkbox" id="chkCompare[]" value="<?php echo $row_rsBrandModel['Brand']; ?>_<?php echo $row_rsBrandModel['Model']; ?>"></td>
    </tr>
    <?php } while ($row_rsBrandModel = mysql_fetch_assoc($rsBrandModel)); ?>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input name="submit" type="submit"></td>
    </tr>
  </table>
  </form>
</body> 
</html>
<?php
mysql_free_result($rsBrandModel);
?>

[phpman]

here you go

<?php
$link = mysql_connect(vm.com, 'user', 'pwd');
mysql_select_db($database_com);
$MainSql = "WHERE ";
error_reporting(E_ALL);
if(isset($_GET) && isset($_GET["chkCompare"])){
    $arIncoming = $_GET["chkCompare"];
    foreach($arIncoming as $k => $v) { 
        if ($k == 0) {
            $arBrandModel = split("_", $v);
            //echo '<br>element['.$k.'] = '.$v;
            $MainSql = $MainSql . "(Brand = '" . $arBrandModel[0] . "' AND Model = '" . $arBrandModel[1] . "') ";
        } else {
            $arBrandModel = split("_", $v);
            //echo '<br>element['.$k.'] = '.$v;
            $MainSql = $MainSql . "OR (Brand = '" . $arBrandModel[0] . "' AND Model = '" . $arBrandModel[1] . "') ";
        }
    }
}

$query_rsVacs = "SELECT * FROM vm_php " . $MainSql;
$rsVacs = mysql_query($query_rsVacs) or die(mysql_error());
$totalRows_rsVacs = mysql_num_rows($rsVacs);
//echo $query_rsVacs;
 while ($row = mysql_fetch_array($rsVacs)){
   $brand[] = $row['Brand'];
   $model[] = $row['Model'];
 }

?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php
$fields = mysql_query("SHOW COLUMNS FROM vm_php");
 while ($row = mysql_fetch_array($fields)){
       $fields2[] = array('Name' => $row[0]);
}
while (list($keyname, $sts_data) = each($fields2)) {
                 $field_name[] = $sts_data['Name'];
}
mysql_free_result($fields);
$columns = count($field_name);

echo"<table>\n";

foreach ($field_name AS $key){
    echo"<tr>";
    echo "<td>".$key." - ";
        for($r=0; $r<$totalRows_rsVacs; $r++){
            if($key == "Brand"){
                echo $brand[$r]." ";
            } else {
                echo $model[$r]." ";
            }
         }
    echo"</td>";
    echo"</tr>";
}
?>
</table>
</body>
</html>

[lleemon]

Thanks phpman. I have a question on the following lines:

while ($row = mysql_fetch_array($rsVacs)){
   $brand[] = $row['Brand'];
   $model[] = $row['Model'];
 } 

You have hard coded the fields here. I need a way to do this dynamically.

Say I have a table named DB_TABLE with 5 fields 4 rows.
Values seperated by commas are:

1, brand1, model1, price1, weight1
2, brand2, model2, price2, weight2
3, brand3, model3, price3, weight3
4, brand4, model4, price4, weight4

I want the recordset to populate the array so if I look at element
array[0][0] it returns the value 1. array[0][3] returns the value 4. array[2][1] returns the value model2

Is something like this possible? This way you should be able to dynamically find the upper limits of the array and loop that many times and you don't need to hard code values.

If you want to see an example of what I want to do, go to the url below and check two or three vacuums and click the compare button. You will then see the result.
http://vacmall.com/vacs.asp?Brand=Sharp

[phpman]

the only thing that is hard coded is the fields from the db. if you don't know field names in the db, then you might be out of luck. I tried a few ways and I only got the first line as well.

I need a way to do this dynamically
why?? you defined them in the query!!

[lleemon]

Here is my query:
SELECT * FROM vm_php .....

I believe the * means all field names in the table.

Thanks for trying to solve my issue. If I ever get this working I will be sure to post the code.

[phpman]

uhh no this is your query

foreach($arIncoming as $k => $v) { 
        if ($k == 0) {
            $arBrandModel = split("_", $v);
            //echo '<br>element['.$k.'] = '.$v;
            $MainSql = $MainSql . "(Brand = '" . $arBrandModel[0] . "' AND Model = '" . $arBrandModel[1] . "') ";
        } else {
            $arBrandModel = split("_", $v);
            //echo '<br>element['.$k.'] = '.$v;
            $MainSql = $MainSql . "OR (Brand = '" . $arBrandModel[0] . "' AND Model = '" . $arBrandModel[1] . "') ";
        }
    } 

you have hard coded fields in there

[lleemon]

phpman,
I am starting to doubt your name. Take a closer look. The code populates a variable that might look like this:
$MainSql = WHERE (Brand = 'Brand1' AND Model = 'Model1') OR (Brand = 'Brand3' AND Model = 'Model3')

You will then see my query is stated as:

$query_rsVacs = "SELECT * FROM vm_php " . $MainSql; 

To me this means concatenates the "SELECT .." string with the $MainSql variable. The $MainSql is the formed WHERE statement. Maybe I should have called the variable $WhereSql.

So the result would be:
SELECT * FROM vm_php WHERE (Brand = 'Brand1' AND Model = 'Model1') OR (Brand = 'Brand3' AND Model = 'Model3')

The Brand and Model are basically the primary keys in the table. Yes, I should probably stick with a numeric field to do this but hey, I am a beginner. We all learn from our mistakes.

And I did get it to work very nicely. I will post the code shortly.

phpman-Thanks for making me think a little deeper. These forums are a great tool.

[lleemon]

Here is my finalized code that works very nicely.

<?php require_once('../Connections/vm_com.php'); ?>
<?php
$MainSql = "WHERE ";
error_reporting(E_ALL);
if(isset($_GET) && isset($_GET["chkCompare"])){
    $arIncoming = $_GET["chkCompare"];
    foreach($arIncoming as $k => $v) { 
        if ($k == 0) {
            $arBrandModel = split("_", $v);
            $MainSql = $MainSql . "(Brand = '" . $arBrandModel[0] . "' AND Model = '" . $arBrandModel[1] . "') ";
        } else {
            $arBrandModel = split("_", $v);
            $MainSql = $MainSql . "OR (Brand = '" . $arBrandModel[0] . "' AND Model = '" . $arBrandModel[1] . "') ";
        }
    }
}

mysql_select_db($database_vm_com, $vm_com);
$query_rsVacs = "SELECT * FROM vm_c_php " . $MainSql;
$rsVacs = mysql_query($query_rsVacs, $vm_com) or die(mysql_error());
$row_rsVacs = mysql_fetch_assoc($rsVacs);
$totalRows_rsVacs = mysql_num_rows($rsVacs);

//count($arrayname) // returns record count
//count($arrayname[0]) // returns count of second dimension, field count.

//populate the main multidimensional array.
$iRecCnt = 0;
do {
    foreach ($row_rsVacs as $key => $value) {
        $VacRecords[$iRecCnt][$key] = $value;
    }
    $iRecCnt++;
 } while ($row_rsVacs = mysql_fetch_assoc($rsVacs)); 
 
$TotRecs = count($VacRecords);         //Get the number or records from the array
$TotFlds = count($VacRecords[0]);      //Get the total number of fields in the array

?>

<html>
<head>
<title>Test Page</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table border=1>
<?php

$link = mysql_connect('something.com', 'user', 'pwd');
$fields = mysql_list_fields("vm_com", "vm_c_php", $link);
$columns = mysql_num_fields($fields);

    //Populate array with field names.
    for ($i = 0; $i < $columns; $i++) {
        $FieldNames[] = mysql_field_name($fields, $i);
    }
    
    $iTDColor = 0;
    //Loop through each field and display all records for this field.    
    for ($iFieldLoop = 0; $iFieldLoop < $TotFlds; $iFieldLoop++) {
            if ($iTDColor == 0) {
                echo "<tr bgcolor=\"#CCCCCC\"><td>" . ereg_replace("_", " ", $FieldNames[$iFieldLoop]) . "</td>"; 
                $iTDColor = 1;
            } else {
                echo "<tr bgcolor=\"#FFFFFF\"><td>" . ereg_replace("_", " ", $FieldNames[$iFieldLoop]) . "</td>";
                $iTDColor = 0;
            }
            for ($iRecLoop = 0; $iRecLoop < $TotRecs; $iRecLoop++) {
                echo "<td>" . $VacRecords[$iRecLoop][$FieldNames[$iFieldLoop]] . "</td>";
            }
            echo "</tr>";
    }
    
?>
</table>
</body>
</html>
<?php

mysql_free_result($rsVacs);
?>

[phpman]

Originally posted by lleemon
phpman,
I am starting to doubt your name. Take a closer look. The code populates a variable that might look like this:
$MainSql = WHERE (Brand = 'Brand1' AND Model = 'Model1') OR (Brand = 'Brand3' AND Model = 'Model3')

You will then see my query is stated as:

$query_rsVacs = "SELECT * FROM vm_php " . $MainSql; 

To me this means concatenates the "SELECT .." string with the $MainSql variable. The $MainSql is the formed WHERE statement. Maybe I should have called the variable $WhereSql.

So the result would be:
SELECT * FROM vm_php WHERE (Brand = 'Brand1' AND Model = 'Model1') OR (Brand = 'Brand3' AND Model = 'Model3')

The Brand and Model are basically the primary keys in the table. Yes, I should probably stick with a numeric field to do this but hey, I am a beginner. We all learn from our mistakes.

And I did get it to work very nicely. I will post the code shortly.

phpman-Thanks for making me think a little deeper. These forums are a great tool.

starting to doubt my name...... WFT. now listen to your self and I am sorry you are sucha beginner and I am stupid but look at this

$MainSql = WHERE (Brand = 'Brand1' AND Model = 'Model1') OR (Brand = 'Brand3' AND Model = 'Model3')

all those bolded words are HARD CODED FIELD NAMES and in your query, no different then when I hard coded them. but if you want to make it harder on your self the **** ya. why have them dynamic, it doesn't prove anything. all that more code for nothing.