Главная » Языки программирования

Sql row to array php

Содержание
  1. Fetch all IDs and put them into an array in PHP
  2. 3 Answers 3
  3. Make an array from SQL query
  4. 4 Answers 4
  5. What is the best way to get MySQL query results as an array in PHP?
  6. 5 Answers 5

Fetch all IDs and put them into an array in PHP

please.. please clarify your question. Are you trying to submit a form and want it in ‘array’ format? name[] ?

3 Answers 3

If we assume that the ID column is just called id , then try the following code (DB connection code ommitted for clarity):

$ids_array = array(); $result = mysql_query(«SELECT id FROM table_name»); while($row = mysql_fetch_array($result))

If you want to sort the IDs ascending or descending, simply add ORDER BY id ASC or ORDER BY id DESC respectively to the end of the MySQL query

What the code above does is iterate through every row returned by the query. $row is an array of the current row, which just contains id , because that’s all we’re selecting from the database ( SELECT id FROM . ). Using $ids_array[] will add a new element to the end of the array you’re storing your IDs in ( $ids_array , and will have the value of $row[‘id’] (the ID from the current row in the query) put into it.

After the while loop, use print_r($ids_array); to see what it looks like.

You have two options in PHP. The recommended one is using PDO library. You can use PDO:FETCH_COLUMN fetch mode.

// connect via PDO $pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass, [ \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_EMULATE_PREPARES => false ]); $stmt = $pdo->prepare('SELECT id FROM test1'); $stmt->execute(); $ids = $stmt->fetchAll(PDO::FETCH_COLUMN);

An alternative is to use mysqli. Try to avoid mysqli if you can. It is more complicated and it is easier to make a mistake. It doesn’t have the same fetch mode, so you need to iterate over the result and append a single value to an array manually.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); $mysqli = new mysqli('localhost', 'username', 'password', 'dbname'); $mysqli->set_charset('utf8mb4'); // always set the charset $stmt = $mysqli->prepare('SELECT id FROM test1'); $stmt->execute(); $result = $stmt->get_result(); $ids = []; foreach ($result as $row)

Both approaches will give you a one-dimensional array of ids.

Источник

Make an array from SQL query

I’m trying to go to my DB, get info from a bunch of columns based on two variables, and return all the values of all of those columns into a php list (or array), so for example, the final result would be something like $output = array($foo,$bar,$hello); Here is the code I have right now to connect to the DB and my query, I know I am missing the vital code here, hoping someone can help me, thanks.

    phpmysqlsql
)" data-controller="se-share-sheet" data-se-share-sheet-title="Share a link to this question" data-se-share-sheet-subtitle="" data-se-share-sheet-post-type="question" data-se-share-sheet-social="facebook twitter devto" data-se-share-sheet-location="1" data-se-share-sheet-license-url="https%3a%2f%2fcreativecommons.org%2flicenses%2fby-sa%2f3.0%2f" data-se-share-sheet-license-name="CC BY-SA 3.0" data-s-popover-placement="bottom-start">Share
)" title="">Improve this question
asked Aug 7, 2012 at 13:45
4
    2
    You should stop using mysql_* functions. They're being deprecated. Instead use PDO (supported as of PHP 5.1) or mysqli (supported as of PHP 4.1). If you're not sure which one to use, read this SO article.
    – Matt
    Aug 7, 2012 at 13:47
    The already are in an array. $row is an array. If you want a multidimensional array containing all rows, use $rowset[] = $row inside the while loop
    – Michael Berkowski
    Aug 7, 2012 at 13:48
    1
    You put those variables in an array just as you would put any variable into an array. How do you normally do it. The answer to your question is in your question! And as @Matt pointed out you should be using a better database connection method. Your code is likely vulnerable to SQL Injection
    – Cfreak
    Aug 7, 2012 at 13:48
    thanks guys, will also look into new methods of connection
    – Sam Creamer
    Aug 7, 2012 at 13:54
Add a comment|

4 Answers 4

Reset to default
1

Either one of these will work:

while($row = mysql_fetch_array($go))
while($row = mysql_fetch_array($go))

Источник

How to Convert mysql to array result with number

mysql_fetch_array() will be returning both in numeric key and field name as key. But as u request if all the rows are to be aligned then you had to push data into arrays inside the loop.

@Mr Zy. I have added more codes to the answer. Share me with your thought. Added the code for Multiple data and single row of data too.

5 Answers 5

mysql_fetch_assoc() - Fetch a result row as an associative array.This function will return a row as an associative array where the column names will be the keys storing corresponding value.

Description: Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead. mysql_fetch_assoc() is equivalent to calling mysql_fetch_array() with MYSQL_ASSOC for the optional second parameter. It only returns an associative array.

After that you need to do the array_values() so that it converts all the values to array with numeric keys.

array_values — Return all the values of an array

array_values() returns all the values from the array and indexes the array numerically.

If your query returns multiple rows as output you can prefer this method:

$sqlxx = mysql_query("SELECT userName FROM rcpa_users WHERE upline_users = '$selectuname'"); $final_tjans = array(); while($tjans = mysql_fetch_assoc($sqlxx)) < $final_tjans[] = $tjans['userName']; >print_r($final_tjans);

If you code returns only single row as output:

$sqlxx = mysql_query("SELECT userName FROM rcpa_users WHERE upline_users = '$selectuname'"); $tjans = mysql_fetch_assoc($sqlxx)); $final_tjans = array_values($tjans); print_r($final_tjans); 
 "XL", "color" => "gold"); print_r(array_values($array)); ?>

Note: Usage of mysql extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.

Источник

What is the best way to get MySQL query results as an array in PHP?

I'm new to PHP. I have a select statement that returns 100 values for a particular record. I'd like to store these 100 values in an array. Is this the right command to store values that I get from a select statement into an array?

$result = mysql_query("select * from processed1 where record = ('$id') "); $data = array(); while($row = mysql_fetch_array($result)) < $data[] = $row; //IS THIS CORRECT? >

Is there a way where I can avoid typing in the 100 attributes for my table? example : $row[1] . $row[100]

5 Answers 5

If you are going to learn PHP in 2011, let's do it right.

First off, mysql_query or mysql_ anything code is deprecated. Don't use it anymore.

Don't worry - what I am suggesting works great with mysql databases, but it will also work great with any database:

  • PDO is what the PHP community continues to add features to, so I would use that.
  • PDO is also way more powerful, and makes it easier to switch databases later.
  • MYSQLi (the i stands for improved) replaces deprecated mysql_ based queries, but I would definitely go straight to using PDO.
  • You could also easily create an array of objects later with one line change!

Secondly, Phil mentioned fetchAll(). This is the end goal. The other ways simply move thru it one row at a time. This uses a bulldozer instead of a shovel. Note: not the best way of selecting really large amounts of data, as it will use up memory. Otherwise, it is fine.

To get there, use prepared procedures to protect your code from SQL injection attacks.

prepare('SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour'); $sth->bindParam(':calories', $calories, PDO::PARAM_INT); $sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12); $sth->execute(); /* Fetch all of the rows into an array */ print("Fetch all of the remaining rows in the result set:\n"); $result = $sth->fetchAll(); print_r($result); ?>

Источник

Читайте также:  Config php for mysql
Оцените статью
© 2024 Программирование