Storing data from database in a 1d array not 2d

Question

I want to store a column of my database table in an array so I can compare it with an array I have. I am selecting only one column from the table and I am storing all the rows. I've managed to do so but in a 2d array but when comparing the 2d array to my 1d array i get an error. So please help me to convert it into a 1d array or from the begining if I can store the data in a 1d array.

    $array = array();
$serviceId = "SELECT service_id FROM servicesorders WHERE order_id = '$orderId'";
$resultId = mysqli_query($connection, $serviceId) or die(mysqli_error($connection));
while ($serviceIdfinal = mysqli_fetch_assoc($resultId)) {
$array[] = $serviceIdfinal; //the array I used to store the data 
}
var_dump($array);

$isCheckedstring = $_POST['show'];
$isCheckedarray = str_split($isCheckedstring, 4); // the array I want to compare the stored data with
var_dump($isCheckedarray);

the var_dump of the two arrays are as follows:

array( 
    [0]=> array(
        ["service_id"]=> "1"
    )
    [1]=> array(
        ["service_id"]=> "7"
    )
    [2]=> array( 
        ["service_id"]=> "11" 
    ) 
)

And

array(
    [0]=>"0011"
    [1]=>"0012"
)

[Little Bobby](http://bobby-tables.com/) says **[your script is at risk for SQL Injection Attacks](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)**. Learn about [Prepared Statements](http://en.wikipedia.org/wiki/Prepared_statement) for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even **[escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string)** is not safe! — GrumpyCrouton, Jul 31 '17 at 13:36
have you tryed to use fetch_all http://php.net/manual/en/mysqli-result.fetch-all.php, and something like $result_array = array_values($fetched_all_array); ? http://php.net/manual/en/function.array-values.php — Ravshan Abdulaev, Jul 31 '17 at 13:45
what is the reason to compare in php? it can be better to compare in sql, can't it? https://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition — Ravshan Abdulaev, Jul 31 '17 at 13:49
I have a list of services that is generated by the database when a user needs to edit their chosen services in an order I compare the new services chosen with the old ones to update the order database table — Omar Gamal, Jul 31 '17 at 13:55

score 3 · Accepted Answer · answered Jul 31 '17 at 13:37

You are using mysqli_fetch_assoc so you need to fetch the associative column.

You need to change

while ($serviceIdfinal = mysqli_fetch_assoc($resultId)) {
    $array[] = $serviceIdfinal; //the array I used to store the data 
}

to

while ($serviceIdfinal = mysqli_fetch_assoc($resultId)) {
    $array[] = $serviceIdfinal['service_id']; //the array I used to store the data 
}

RedDwarfian · Answer 2 · 2017-07-31T15:23:45.570

When your database returns an array, it's returning an array of rows, each of which is an associative array. It doesn't matter that each row has one element within it, it's still an array of arrays.

You're going to have to convert the $array from one form to another with a foreach loop:

$newArray = [];
foreach($array as $row) {
    // This line here is where you'll do any needed string modifications
    // such as padding with leading zeroes, etc.
    $newArray[] = $row["service_id"]; 
}
var_dump($newArray)

This should net you:

array( 
    [0]=> "1",
    [1]=> "7",
    [2]=> "11"
)

EDIT: Or you should do it in the while loop you're already using when you're fetching it, as Milan pointed out in his answer.

score 0 · Answer 3 · answered Jul 31 '17 at 13:49

0

You can try to use PDO instead of mysqli.

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$sql = $conn->query("your query");
$sql->fetch(PDO::FETCH_ASSOC);

That's it :)

answered Jul 31 '17 at 13:49

3lvinaz

113
3
12

PDO still returns an array of rows, and each row is an array. It doesn't solve the issue. – RedDwarfian Jul 31 '17 at 15:20

Storing data from database in a 1d array not 2d

3 Answers3