CODEWITHSUNDEEP
phpmysql
user4747724
user4747724

Reputation:

How can I get this php to return the entire column of an sql db

I am trying to query a db for an entire column of data, but can't seem to get back more than the first row.

What I have so far is:

$medicationItem = array();
$medicationItemSql = "SELECT medication FROM medication";
$medicationItemObj = mysqli_query($connection, $medicationItemSql);
if($row = mysqli_fetch_array($medicationItemObj, MYSQLI_NUM)){
    echo count($row);
}

It's not my intention to just get the number of rows, I just have that there to see how many it was returning and it kept spitting out 1.

When I run the sql at cmd line I get back the full result. 6 items from 6 individual rows. Is mysqli_fetch_array() not designed to do this?

Upvotes: 1

Views: 127

Answers (5)

Gayathri
Gayathri

Reputation: 126

You can use mysqli_fetch_assoc() as below.

while ($row = mysqli_fetch_assoc($medicationItemObj)) {
   echo $row['medication'];
}

Upvotes: 0

varad mayee
varad mayee

Reputation: 619

You can wrote your code like below 

$medicationItem = array();
$medicationItemSql = "SELECT medication FROM medication";
$medicationItemObj = mysqli_query($connection, $medicationItemSql);
while ($row = mysqli_fetch_assoc($medicationItemObj)) 
{
    echo $row['medication'];
}

I think this you want

Upvotes: 1

PublisherName
PublisherName

Reputation: 130

Well, I had a hard time understanding your question but i guess you are looking for this.

$medicationItem = array();
$medicationItemSql = "SELECT medication FROM medication";
$medicationItemObj = mysqli_query($connection, $medicationItemSql);

if($row = mysqli_num_rows($medicationItemObj))
{
    echo $row;
}

Or 

$medicationItem = array();
$medicationItemSql = "SELECT medication FROM medication";
$medicationItemObj = mysqli_query($connection, $medicationItemSql);
$i = 0;
while ($row = mysqli_fetch_array($medicationItemObj))
{
    $medicationItem[] = $row[0];
    $i++;
}
echo "Number of Rows: " . $i;

If you just want the number of rows i would suggest using the first method.

http://php.net/manual/en/mysqli-result.num-rows.php

Upvotes: 1

Full Stack Alien
Full Stack Alien

Reputation: 12511

First, I would use the object oriented version of this and always use prepared statements!

//prepare SELECT statement
$medicationItemSQL=$connection->prepare("SELECT medication FROM medication");
// execute statement
$medicationItemSQL->execute();
//bind results to a variable
$medicationItemSQL->bind_result($medication);
//fetch data
$medicationItemSQL->fetch();
//close statement
$medicationItemSQL->close();

Upvotes: 0

Kyle
Kyle

Reputation: 403

You could give this a try:

$results = mysqli_fetch_all($medicationItemObj, MYSQLI_NUM);

Upvotes: 0

Related Questions