Need help ordering data from mysql_fetch_array()

Question

I'm having a hard time organizing the data that I get from mysql_fetch_array().

I have a DB table with that looks something like this:

+---------------------+------------------+---------------+--------+---------+
| date                | name             | indexed_pages | nameID | entryID |
+---------------------+------------------+---------------+--------+---------+
| 2012-06-15 21:18:06 | site1.com        |           200 |      1 |       1 |
| 2012-06-15 21:18:10 | site2.com        |            25 |      2 |       1 |
| 2012-06-15 21:18:13 | site3.com        |            12 |      3 |       1 |
| 2012-06-15 21:18:16 | site4.com        |             8 |      4 |       1 |
| 2012-06-15 21:18:19 | site5.com        |             2 |      5 |       1 |
| 2012-06-16 00:11:12 | site1.com        |           191 |      1 |       2 |
| 2012-06-16 00:11:21 | site2.com        |            25 |      2 |       2 |
| 2012-06-16 00:11:30 | site3.com        |            12 |      3 |       2 |
| 2012-06-16 00:11:44 | site4.com        |             8 |      4 |       2 |
| 2012-06-16 00:11:51 | site5.com        |             2 |      5 |       2 |
| 2012-06-18 10:20:47 | site1.com        |           191 |      1 |       3 |
| 2012-06-18 10:20:52 | site2.com        |            25 |      2 |       3 |
| 2012-06-18 10:20:56 | site3.com        |            12 |      3 |       3 |
| 2012-06-18 10:21:00 | site4.com        |             8 |      4 |       3 |
| 2012-06-18 10:21:04 | site5.com        |             2 |      5 |       3 |
+---------------------+------------------+---------------+--------+---------+

I need to order the results in a Google Line Graph in the following manner:

['date', 'site1_entryID=1', 'site2_entryID=2', 'site3_entryID=3', (...)],";

The thing is that I'm having trouble managing the arrays that I generate. I'm using the following code:

mysql_connect("host_here", "username_here", "pass_here") or die(mysql_error());
mysql_select_db("my_database") or die(mysql_error());
$query = "SELECT * FROM pages";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

After this I need to echo the number of indexed_pages for each site where entryID = 1.

I don't know if this description is confusing or not, but I've tried pretty much everything and can't get the organize the data from the arrays to serve what I need to do. Help, please!

Thanks in advance!

Marc B · Accepted Answer · 2012-07-03 19:36:27Z

4

Don't use select *, that's lazy, and you're stuck accepting the fields in the order the DB decides to produce them in.

Specify the fields you want, in the order you want:

SELECT date, name, indexed_pages, etc...

answered Jul 3, 2012 at 19:36

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

1 Comment

Haddock-san Over a year ago

Hey, @marc-b. Yeah, I did that as well but to no avail.<br/> I tried: SELECT date, name, indexed_pages, nameID, entryID FROM pages; but still wasn't able to use the data output from mysql_fetch_array() since it orders it in a way that I can't handle :S

ashah142 · Accepted Answer · 2012-07-03 20:13:06Z

1

I think the simplest query is :

$result= mysql_query("SELECT name, index_pages, entryID from table_name WHERE entryID =    
1");
while($row=mysql_fetch_array($result)){
    echo "$row[name]";
    echo "$row[index_pages]";
    echo "$row[entryID]";
}

Try this. There might be some mistakes. Because i developed it fast. And replace table_name with yours.

Or you can display it in a table:

echo "<table>";
echo "<tr><td>Sit Name</td>";
echo "<td>Page Name</td>";
echo "<td>EntryID</td>";
echo "</tr>";
while($row=mysql_fetch_array($result)){
    echo "<tr>";
    echo "<td>$row[name]</td>";
    echo "<td>$row[index_pages]</td>";
    echo "<td>$row[entryID]</td>";
    echo "</tr>";
}
echo "</table>";

edited Jul 3, 2012 at 20:13

answered Jul 3, 2012 at 20:03

ashah142

5801 gold badge5 silver badges12 bronze badges

Comments

Nir Alfasi · Accepted Answer · 2012-07-03 19:42:33Z

0

SELECT date, name, indexed_pages  
FROM pages 
where entryID=1 
order by date asc ,name asc

edited Jul 3, 2012 at 19:42

answered Jul 3, 2012 at 19:37

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

Comments

Mark Seah · Accepted Answer · 2012-07-03 19:46:25Z

0

Not sure if this will help

 mysql_connect("host_here", "username_here", "pass_here") or die(mysql_error());
mysql_select_db("my_database") or die(mysql_error());
$query = "SELECT * FROM pages";
$result = mysql_query($query);
$data[]='date';
while($row = mysql_fetch_assoc($result)){
   $name=substr($row['name'], -4);
   $data[]= $name."_entryID=".$row['entryID'];
}

A little of a brute force method.

answered Jul 3, 2012 at 19:46

Mark Seah

5325 silver badges19 bronze badges

Collectives™ on Stack Overflow

Need help ordering data from mysql_fetch_array()

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related