MySQL Returns Only One Row

MySQL returns only one row

$query = mysql_query("SELECT `title`,
                             `url_title`
                        FROM `fastsearch`
                       WHERE `tags`
                            LIKE '%$q%'
                       LIMIT 5");

while ($row = mysql_fetch_assoc($query)) {
    print_r($row);
}

• You misspelled $query in your example
• mysql_fetch_assoc() will return a row each time it is called, and FALSE when out of rows. Use that to your advantage, by assigning a variable to it in the condition. Within the while() loop, $row will be the current row.

Return One Row from MySQL

Add limit 0,1 and for PHP 7 use mysqli

$query = "SELECT name,age FROM people WHERE uid = '2' LIMIT 0,1";
$result = mysqli_query($query);
$res = mysqli_fetch_assoc($result);

echo $res["age"]; 

MySQL returning only 1 row, multiple entries in the database

Your select statement includes group_concat(). This is an aggregation function. Because there is no group by, this means that all the rows are treated as one group, and hence one row is returned.

Perhaps you mean:

SELECT publicationsId, title, summary, image, publicationDate, citation, dateAdded,
       GROUP_CONCAT(DISTINCT tagName) AS tagName,
       GROUP_CONCAT(DISTINCT publicationType) AS publicationType,
       employee_employeeId
FROM publications
LEFT JOIN publications_have_publicationTags ON publications_have_publicationTags.publications_publicationsId = publications.publicationsId
LEFT JOIN publicationTags ON publications_have_publicationTags.publicationTags_publicationTagId = publicationTags.publicationTagId
LEFT JOIN publications_have_publicationTypes ON publications.publicationsId = publications_have_publicationTypes.publications_publicationsId
LEFT JOIN publicationTypes ON publications_have_publicationTypes.publicationTypes_publicationTypeId = publicationTypes.publicationTypeId
LEFT JOIN employee_has_publications ON publications.publicationsId = employee_has_publications.publications_publicationsId
GROUP BY publicationsId, title, summary, image, publicationDate, citation, dateAdded, emplyee_employeeid
ORDER BY dateAdded DESC;

MySQL Average Query only returning one row

Just add: GROUP BY hotels.hotel_id assuming it is the PK.

Return only 1 record

Use GROUP_CONCAT() like this:

SELECT personal.city, GROUP_CONCAT(technologies.tech)
FROM personalINNER JOIN technologies 
ON technologies.uid = personal.uid
WHERE personal.uid = 2
GROUP BY personal.city

You can also set the order of the items by:

GROUP_CONCAT(technologies.tech ORDER BY technologies.tech)

or choose another column for the order.

MySQL SELECT Statement Only Returns One Row

You are on the right track with the joined subquery, but you need to apply the COUNT() inside the subquery. Otherwise, the aggregate group is applied to the outer query, resulting in one row.

This is because you have no GROUP BY, which MySQL is lenient about. It gives you one row back with not quite the results you expect, where most other RDBMS would give you a parse error. Otherwise, an aggregate function (COUNT(),MIN(),MAX(),SUM(), etc) with no GROUP BY will never result in more than one row returned.

SELECT 
  topics.id, 
  topics.authorID,
  topics.lastReply,
  postsInTopic
FROM 
  ms_topics as topics
  /* The subquery provides the aggregate count
     grouped per inTopic */
  INNER JOIN (
    SELECT
     inTopic,
     COUNT(*) AS postsInTopic
   FROM ms_posts
   GROUP BY inTopic
  ) as rcount ON rcount.inTopic = topics.id
ORDER BY
  topics.lastReply DESC,
  /* Use the alias from the subquery postsInTopic to srot */
  postsInTopic

A quick note about PDO and prepare(): While it is a good thing that it's in your workflow, it is not strictly necessary to use prepare()/execute() if your query has no input values (no dynamic WHERE clause, for example). For a static query in a case like this, you can safely call PDO::query() with less overhead and a little less code.

Mysql returns only one row when using Count

Yeah, the count is an aggregate operator, which makes only one row returned (without a group by clause)

Maybe make two separate queries? It doesn't make sense to have the row return the data and the total number of rows, because that data doesn't belong together.

If you really really want it, you can do something like this:

SELECT *, (select count(*) FROM notis WHERE cid=20) AS count FROM notis WHERE cid=20 ORDER BY nid DESC LIMIT 0,3

or this:

SELECT N.*, C.total from notis N join (select count(*) total FROM notis WHERE cid=20) C WHERE cid=20) AS count FROM notis WHERE cid=20 ORDER BY nid DESC LIMIT 0,3

With variances on the nested expression depending on your SQL dialect.

Why is MySQL SUM query only returning one row?

You need to group by something, probably handle (I presume that is related to the account id?), otherwise MySQL will SUM all the values selected from your JOIN. Adding a GROUP BY clause makes the SUM happen for each distinct value of the column in the GROUP BY. Change your query to:

SELECT SUM(balance), handle FROM statement_versions
INNER JOIN statements ON statement_versions.statement_id = statements.id
INNER JOIN accounts ON statements.account_id = accounts.id
GROUP BY handle;

If handle is not related to accounts.id and you want to get the results grouped by accounts.id, change the query to:

SELECT SUM(balance), accounts.id FROM statement_versions
INNER JOIN statements ON statement_versions.statement_id = statements.id
INNER JOIN accounts ON statements.account_id = accounts.id
GROUP BY accounts.id;

Why PHP function is returning Only the one row from mysql

The return should be outside the while loop !

Here is how you can get all the rows :

     function getAll()
     {
        global $con;

        $SQL  = "SELECT * FROM users";

        $stmt = $con->query($SQL);

        if($stmt->num_rows > 0){

            $arr = array();

            while($row = $stmt->fetch_row())
            {
                $arr[] = $row;
            }
            return $arr;
        }
     }

Or you can do something like this return a generator and loop through :

function getAll()
     {
        global $con;

        $SQL  = "SELECT * FROM users";

        $stmt = $con->query($SQL);

        if($stmt->num_rows > 0){


            while($row = $stmt->fetch_row())
            {
                yield $row;
            }
        }
     }

see the result here !

echo '<pre>';

foreach (getAll() as $value) {
 print_r($value);
}

MySQL SELECT query only returns one row

In each iteration you are overwriting or recreating array $child, hence at the end you just have only one element (last row of iteration) in array $child.

Change

$child = array(
              $this->name = $child_id->row['firstname'],
               $this->surname = $child_id->row['lastname']
           );

To

( Push one or more elements onto the end of array in each iteration )

$child[] = array(
              $this->name = $child_id->row['firstname'],
               $this->surname = $child_id->row['lastname']
           );

You can also use array_push like below

array_push(
           $child,
           array(
                  $this->name = $child_id->row['firstname'],
                   $this->surname = $child_id->row['lastname']
               )
           );

---

Related Topics