0

I have a table that includes 53 rows, in my database. The variables in the rows are INT id, VARCHAR var, and INT votes. I can't figure out how to query all the rows within the table into an array. I want to be able to get the 'var' variable while referencing the id. Here is what I have so far, which indeed will only query the first row. (hence id = '1').

<?php

include_once "code.php";
$new_array = array();
$sql = mysql_query("SELECT * FROM Variables WHERE id='1'");
while ($row = mysql_fetch_array($sql)) {
    $new_array[$row['id']] = $row;
};

I tried using a for loop and sticking a variable where the '1' is, but that's not working either. If you could please also show me how to get the var variable from referencing any of the ids that be great. Thanks

xxx
  • 1,153
  • 1
  • 11
  • 23
LearningProcess
  • 607
  • 1
  • 8
  • 29
  • How PhpMyAdmin is connected with your PHP code? – Vlad DX Oct 27 '15 at 16:24
  • you don't query phpmyadmin. that's a management interface. you query the mysql database... and if you want all the records in the table, then don't have a `where` clause in the first place. SQL is one of the few things in life where the less you specify, the more you get. – Marc B Oct 27 '15 at 16:27
  • 1
    @VladimirSerykh Like so: – LearningProcess Oct 27 '15 at 16:28
  • It's not about PhpMyAdmin at all. – Vlad DX Oct 27 '15 at 16:29
  • 1
    @MarcB Could you show me a example of how to reference the var's based on ID's once I get query without the `WHERE`? – LearningProcess Oct 27 '15 at 16:29
  • YOu keep re-initializing your `new_array` inside the loop, so effectively you'll only ever get the LAST record. – Marc B Oct 27 '15 at 16:31
  • 1
    @MarcB I put the `new_array` outside of the loop. How do i grab all the rows and put them into the array, while still being able to reference the array by the 'id' to get the 'var'? – LearningProcess Oct 27 '15 at 16:38
  • exactly as you are. you just needed to stop destroying previously done work. – Marc B Oct 27 '15 at 16:46

1 Answers1

0

You declare $new_array for each row. You should move it outside the while loop. Also, try not to use mysql_* functions because they are deprecated. Try mysqli_* or even better PDO.

include_once "code.php";
$sql = mysql_query("SELECT * FROM Variables");
$new_array = array(); //This array should be not inside while
while($row = mysql_fetch_array($sql)){
    $new_array[$row['id']] = $row;
    //Do something else here if you want
};

Using PDO

try {
    $link= new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);
    echo "PDO connection object created";
}
catch(PDOException $e) {
    echo $e->getMessage();
    die();
}

$sql = "SELECT * FROM users";
//Prepare your query
$stmt = $link->prepare($sql);
//Execute your query binding variables
$result = $stmt->execute(array());
$result = $stmt->fetchAll();
$new_array = array();
foreach ($result as $row) {
    $new_array[$row['id']] = $row;
}

Edit after the comment:

Remove the WHERE id = '1' from you query and you will have all your rows. Then you can take the var referenced by id using:

echo $new_array[1]['var'];
echo $new_array[2]['var'];
Kostas Mitsarakis
  • 4,772
  • 3
  • 23
  • 37