Query a MySQL Table, store in PHP array, then return a single value from the array

Question

I have a table "rel"

---------------------
|id|refid|cellid|cat|
|1 |1    |1     |1  |
|2 |2    |1     |3  |
|3 |3    |1     |5  |
|4 |3    |5     |2  |
|5 |4    |2     |7  |

I want to count the number of rows with the same cellid for each cellid. Then have those counts retrievable in a HTML page.

I've written code to run the SQL query and store the results in an array

<?php
// Connect to the database server
$dbcnx = mysql_connect("localhost",USER,PASSWORD);
if (!$dbcnx) {
  echo( "<P>Database Connection Failed</P>" );
  exit();
}
// Select the matrix databse database
  if ( !@mysql_select_db("DATABASE") ) {
    echo( "<P>Not connected to Database</P>" );
    exit();
  }

  $cell_array = array();
  if ($result = mysql_query("SELECT cellid, COUNT(*) FROM rel GROUP BY cellid")) {
    if (mysql_num_rows($result)) {
      while ($row = mysql_fetch_assoc($result)) {
        $array[] = $row;
      }
    }
  }
?>

I then want to access this php script from another page using require, and then recover the count result for any cellid so that I can put the results in a HTML Table.

Is this the right way to approach this problem and if so what is the syntax of recalling values from this array, I have seen

echo $cell_array['cellid'];

used on other answers but it does not seem to work for me.

Thanks

Rick Burgess · Accepted Answer · 2012-10-18 13:22:40Z

1

You are adding the rows to a new array called "$array" not "$cell_array" so $cell_array is empty.

give count(*) an alias as mentioned by John and then

change

$array[] = $row;

to

$cell_array[$row["cellid"]] = $row['totalcount'];

to pull a value out use:

echo $cell_array[x];

where x is the cellid you want a count for.

also I assume you wanted to use a Constant for the "DATABASE" so change

if ( !@mysql_select_db("DATABASE") ) {

to

if ( !@mysql_select_db(DATABASE) ) {

answered Oct 18, 2012 at 13:22

Rick Burgess

7351 gold badge5 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

falcs Over a year ago

Thanks, but still isn't working I tried var_dump($cell_array); to see what was in the array and got a lot of nulls array(6) { [0]=> NULL [1]=> NULL [2]=> NULL [3]=> NULL [4]=> NULL [1458]=> NULL }

falcs Over a year ago

Sorry fixed problem, was due to typo in naming the ALIAS

John Woo · Accepted Answer · 2012-10-18 13:16:14Z

1

the column COUNT(*) needs to have an ALIAS so you can fetch the value form it

SELECT cellid, COUNT(*) totalCount
FROM rel 
GROUP BY cellid

so in PHP you can access

while ($row = mysql_fetch_assoc($result)) 
{
    echo $row["cellid"];
    echo $row["totalCount"];
}

answered Oct 18, 2012 at 13:16

John Woo

265k70 gold badges509 silver badges500 bronze badges

Collectives™ on Stack Overflow

Query a MySQL Table, store in PHP array, then return a single value from the array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related