Jump to content


Photo

PHP Mysqli making the results from SELECT * into an array


Best Answer mac_gyver, 02 May 2013 - 03:19 PM

you must bind a variable to each field that is selected. by using SELECT *, no one knows just by looking at the query how many fields there actually are and there are apparently more than the 5 fields that were listed.

 

you need to SELECT the actual fields that you want in the query so that you will have the information in front of you to bind all the selected fields.

Go to the full post


  • Please log in to reply
9 replies to this topic

#1 james909

james909

    Advanced Member

  • Members
  • PipPipPip
  • 61 posts

Posted 02 May 2013 - 03:18 AM

here is my code for selecting one row by the ID:

$mysqli = new mysqli("localhost", "levelDbUser", "honey", "levelDb");

$outpoststatement = $_GET['level'];

$statement = $mysqli->prepare("SELECT * FROM users WHERE 'id' =  ?");

$statement->bind_param("i",$outpoststatement);

$statement->execute();

Now how to I get the results from this row into an array of the columns:

$array[user_type]

$array[username]

$array[average_value]
$array[total_value]
$array[description]


#2 trq

trq

    Advanced Member

  • Administrators
  • 31,022 posts
  • LocationSydney, Australia.

Posted 02 May 2013 - 04:03 AM

See http://php.net/manua....get-result.php

http://thorpesystems.com | http://proemframework.org | http://github.com/trq

SmtpCatcher - A very simple mock sendmail useful for testing PHP mail scripts.
OPM - My Linux package manager.


#3 james909

james909

    Advanced Member

  • Members
  • PipPipPip
  • 61 posts

Posted 02 May 2013 - 04:38 AM

thank you trq for the link, i have made a loop to get the results into an array, this is my code:

$mysqli = new mysqli("localhost", "levelDbUser", "honey", "levelDb");

$outpoststatement = $_GET['level'];

$statement = $mysqli->prepare("SELECT * FROM users WHERE 'id' =  ?");

$statement->bind_param("i",$outpoststatement);

$statement->execute();

$level_array = array('user_type','username','average_value','total_value','description');

    foreach($level_array as $level)
    {
        $statement->execute();
        $result = $statement->get_result();
        while ($column = $result->fetch_array(MYSQLI_NUM))
        {
            foreach ($column as $c)
            {
               $array[$column] = $c;
            }
        
        }
    }

and it is returning this error: Fatal error: Call to undefined method mysqli_stmt::get_result()



#4 trq

trq

    Advanced Member

  • Administrators
  • 31,022 posts
  • LocationSydney, Australia.

Posted 02 May 2013 - 04:49 AM

The error makes little sense. It definitely exists.

http://thorpesystems.com | http://proemframework.org | http://github.com/trq

SmtpCatcher - A very simple mock sendmail useful for testing PHP mail scripts.
OPM - My Linux package manager.


#5 james909

james909

    Advanced Member

  • Members
  • PipPipPip
  • 61 posts

Posted 02 May 2013 - 05:29 AM

i done some researching and i think the problem is this method was introduced in php 5.3 and my host is running PHP Version 5.2.17

 

is there a known method for getting the results from the database select * into an array in this version of PHP?



#6 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,381 posts
  • LocationCheshire, UK

Posted 02 May 2013 - 05:30 AM

What PHP version are you using.

 

 


mysqli_stmt::get_result      mysqli_stmt_get_result

(PHP 5 >= 5.3.0)


Edited by Barand, 02 May 2013 - 05:36 AM.

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#7 InoBB

InoBB

    Advanced Member

  • Members
  • PipPipPip
  • 35 posts

Posted 02 May 2013 - 12:32 PM

You can get your results into an array by manually setting them into the array after your query:
$mysqli = new MySQLi('localhost', 'levelDbUser', 'honey', 'levelDB');

if (!$connection) {
    echo "Database connection failed:" . mysqli_errno();
    exit();
}
if (isset($_GET['level'])) {
	
    $outpoststatement = $_GET['level'];
	
    $sql = "SELECT * FROM users WHERE id =  ?";
    $statement = $mysqli->prepare($sql);
    $statement->bind_param("s", $outpoststatement);
    $statement->bind_result($usertype, $username, $average_value, $total_value, $description);
    $statement->execute();
    $statement->store_result();
    while ($statement->fetch()) {
	$level_array = array(
	    'user_type'		=> $usertype,
	    'username'		=> $username,
	    'average_value'	=> $average_value,
	    'total_value'	=> $total_value,
	    'description'	=> $description
	    );
	foreach ($level_array as $level) {
	    //run script with your newly formed array(s)
            var_dump($level);
	}
    }
}

Edited by InoBB, 02 May 2013 - 12:32 PM.


#8 james909

james909

    Advanced Member

  • Members
  • PipPipPip
  • 61 posts

Posted 02 May 2013 - 03:00 PM

thank inoBB

 

it is returning this error: Warning: mysqli_stmt::bind_result() [mysqli-stmt.bind-result]: Number of bind variables doesn't match number of fields in prepared statement

 

and the array values are all: NULL



#9 mac_gyver

mac_gyver

    Advanced Member

  • Administrators
  • 2,547 posts

Posted 02 May 2013 - 03:19 PM   Best Answer

you must bind a variable to each field that is selected. by using SELECT *, no one knows just by looking at the query how many fields there actually are and there are apparently more than the 5 fields that were listed.

 

you need to SELECT the actual fields that you want in the query so that you will have the information in front of you to bind all the selected fields.


Edited by mac_gyver, 02 May 2013 - 03:20 PM.

multi-purpose programming fool. well written source-code should be self-documenting. well written code should be self-troubleshooting. 


#10 james909

james909

    Advanced Member

  • Members
  • PipPipPip
  • 61 posts

Posted 02 May 2013 - 03:53 PM

fixed it! thank you for all the help trq, barand, inobb, mac_gyver. great help on phpfreaks as always






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com