Array in for or foreach from database

[sigmahokies]

Hi everyone,

 

I am still learning PHP. I am trying to get data from database which "show tables from (ojbect)" that pour into array, then set them up in the select and option in HTML, I can't figure why it won't work to get data from table to get in loop as for and foreach. I don't think "[ ]" will help in PHP, will it? Can anyone please help me? thank you so much!

 

$show = "show tables from XXXX";    $table = mysqli_query($GaryDB, $show);    while($row = mysqli_fetch_array($table)) {        $tables = array($row);    foreach($tables as $select) {        for($i = 0; $i < count($select); $i++) {        $list = "<option name='choose' value='".$select[i]."'>".$select[i]."</option>";        }    }    }

Edited March 5, 2018 by sigmahokies

[sigmahokies]

Hi everyone again, I made mistake...but still not work.

 

here my recode in PHP

 

$show = "show tables from XXXX";    $table = mysqli_query($GaryDB, $show);    while($row = mysqli_fetch_array($table)) {        $tables[] = $row;        for($i = 0; $i < count($tables); $i++) {            $list = "<option value='".$tables[$i]."'>".$tables[$i]."</option>";        }    }

Edited March 5, 2018 by sigmahokies

[mac_gyver]

i recommend a programming practice called separation of concerns. the database specific code, that knows how to execute the query and fetch the data, is one concern, and the presentation code, that knows how to produce the output from the data, is a separate concern.

 

the way to separate these is to just fetch the data into an appropriately named php variable, then use that variable as the input to the presentation code.

 

doing this will make it easier to design, write, and test code, because you can display the output from the first step to make sure it is correct, before going on to the next step.

 

see the the following pseudo code - 

// database specific code
$sql = the_sql_query_statement;
$result = mysqli_query($GaryDB, $sql);
$table_result = [];
while($row = mysqli_fetch_array($result))
{
    $table_result[] = $row;
}

// examine the data
print_r($table_result);


// presentation code
foreach($table_result as $row)
{
    // use elements in $row to produce the output
}

Edited March 5, 2018 by mac_gyver

[Psycho]

Agree with mac_gyver!

 

As to your code. It is running a DB query and then looping over each record (which is an array). Then for each record you are appending the record to the previous list of records as a multidimensional array. Then, finally, you are (within that loop) iterating over each record in the array to add the record to an output variable.

 

There are several problems here:

 

A) Because you are adding each record to the multi-dimensional array AND outputting the array within the loop, if it was working, you would get results like this: 1, 1, 2, 1, 2, 3, 1, 2,3 ,4  . . . 

 

B) When tryign to output a record, you are referencing the array (that was retrieved via mysqli_fetch_array). You need to reference a value in the result set.

 

C) You are redefining the value of the output variable on each loop. You should be appending the new record output to the existing output.

 

D) While it does work, there is no need to do a for() loop on an array with a random variable - use a foreach() loop.

 

Building upon mac_gyver's post:

 

<?php
 
// Query the database for the needed records
// - suggest putting SQL commands in CAPS
$sql = "SHOW TABLES FROM XXXX";
$result = mysqli_query($GaryDB, $sql);
//Dump database results into array
$table_result = [];
while($row = mysqli_fetch_array($result))
{
    // Add the table NAME to the array: $row[0]
    // - Not the array
    $table_result[] = $row[0];
}
 
// Examine the data
print_r($table_result);
 
 
// Iterate over the results to create the output
$table_options = '';
foreach($table_result as $table)
{
    //Append this record ot the output variable
    $table_options .= "<option value='{$table}'>{$table}</option>\n";
}
 
// Output the content within the presentation (HTML)
// The above code should exist at the top of the page or in a separate file
 
?>
<html>
<body>
Tables:
<select name="tables">
  <?php echo $table_options; ?>
</select>
</body>
</html>

[sigmahokies]

It works!!! finally...Now, I can building the SQL execute!

 

Thank you so much!