visitor counter

[jumphopper]

Hi im currently trying to categorize the results by date.

however my code displays this:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/virtual/site23/fst/var/www/projects/aktest/date.php on line 92

can you see anything wrong with my line 92?

<p><strong>Sort Records By Date</strong>
<form name="form1" method="post" action="<?php $_SERVER['PHP_SELF']?>">
<table width="200" border="1">
<tr>
<td><label><strong>Month</strong></label></td>
<td><label><strong>Date</strong></label></td>
<td><label><strong>Year</strong></label></td>
</tr>
<tr>
<td><select name="MONTH">
<option value="01" selected="selected">January</option>
<option value="02">Feburary</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select></td>
<td><select name="DAY">
<option value="01" selected="selected">01</option>
<option value="02">02</option>
<option value="03">03</option>
<option value="04">04</option>
<option value="05">05</option>
<option value="06">06</option>
<option value="07">07</option>
<option value="08">08</option>
<option value="09">09</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select></td>
<td><select name="YEAR">
<option value="2006" selected="selected">2006</option>
</select></td>
</tr>
</table>
<input name="submit" type="submit" value="submit"></form><br />
<br />

<?php

require('sql.php');

if (isset($POST['submit'])) {
$DAY = $POST['DAY'];
$MONTH = $POST['MONTH'];
$YEAR = $POST['YEAR'];
}

if (isset($Submit))
{
$query = "SELECT * FROM tracker WHERE timespan LIKE '$YEAR-$MONTH-$DAY%'";
$res = mysql_query($query);

}
echo "<table border='1'>\n
\t<tr><th>id</th><th>home</th><th>start</th><th>end</th><th>timespan</th></tr>\n";
while($row = mysql_fetch_array($res))
echo "
\t<tr>\n
\t\t<td>".$row['id']."</td>\n
\t\t<td>".$row['home']."</td>\n
\t\t<td>".$row['start']."</td>\n
\t\t<td>".$row['end']."</td>\n

\t\t<td>".$row['timespan']."</td>\n
\t</tr>\n\n";
echo "
</table>";

?>

[bpat1434]

The problem is not with the mysql_fetch_array, but with your query. Your query isn't returning results, so there is no row to fetch to an array.

It should work (i.e. I just tested your exact query with my table and it works (mySQL 5)). Just make sure that there is a date in the database that corresponds to the date you are querying for.

This points out some flaws in your code: You should account for the fact that there might not be stats for a certain day yet. That's why you always check the mysql_num_rows result so if there aren't rows, you can go ahead and modify your code accordingly so you don't get errrors....

[jumphopper]

am i displaying the date right?

if (isset($_POST['submit']))  {
$YEAR = $_POST['YEAR']; 
$MONTH = $_POST['MONTH']; 
$DAY = $_POST['DAY'];
}


if (isset($Submit))
{
$query = "SELECT * FROM tracker WHERE timespan LIKE 'YEAR-MONTH-DAY%'";

[jumphopper]

brett, im totally lost 🙁...
here is my attempt to mysql_num_row... not looking very good. i have no clue where to start. i've never used this command before.

<?php

require('sql.php');

if (isset($_POST['submit']))  {
$YEAR = $_POST['YEAR']; 
$MONTH = $_POST['MONTH']; 
$DAY = $_POST['DAY'];
}


if (isset($Submit))
{
$result=mysql_query("SELECT * FROM tracker WHERE start = '1' ORDER BY id DESC LIMIT 1") or die ("Cannot execute the query");
//$query = "SELECT * FROM tracker WHERE timespan LIKE 'YEAR-MONTH-DAY %'";
$result = mysql_query($query);

}
echo "<table border='1'>\n 
\t<tr><th>id</th><th>home</th><th>start</th><th>end</th><th>timespan</th></tr>\n";
if (mysql_num_rows($result)>0) { 
    while($row=mysql_fetch_array($result)) { 
	echo " 
\t<tr>\n 
\t\t<td>".$row['id']."</td>\n 
\t\t<td>".$row['home']."</td>\n
\t\t<td>".$row['start']."</td>\n 
\t\t<td>".$row['end']."</td>\n  

\t\t<td>".$row['timespan']."</td>\n 
\t</tr>\n\n"; 
    echo " 
</table>"; 

}
}
?>

[bpat1434]

This:

"SELECT * FROM tracker WHERE timespan LIKE 'YEAR-MONTH-DAY%'";

should be:

"SELECT * FROM tracker WHERE timespan LIKE '".$YEAR."-".$MONTH."-".$DAY."%'";

[jumphopper]

i still get the error msg even with the SELECT update...
i dont see what im doing wrong... 😕

[bpat1434]

add an "or die(mysql_error())" statement to the query call.

[jumphopper]

awesome everything is working! 🙂
but can you further explain to me how to get the "mysql_num_rows" command to work?
so that i dont have a big list of 0-1-0's?

here is my current code

<?php

require('sql.php');

if (isset($_POST['submit']))  {
$YEAR = $_POST['YEAR']; 
$MONTH = $_POST['MONTH']; 
$DAY = $_POST['DAY'];
}


if (isset($submit))
{
$query = "SELECT * FROM tracker WHERE timespan LIKE '".$YEAR."-".$MONTH."-".$DAY."%'"; 
$res = mysql_query($query) or die("couldn't perform $query");
}
echo "<table border='1'>\n 
\t<tr><th>id</th><th>home</th><th>start</th><th>end</th><th>timespan</th></tr>\n";

while($r=mysql_fetch_array($res)) { 
    echo " 
\t<tr>\n 
\t\t<td>".$r['id']."</td>\n 
\t\t<td>".$r['home']."</td>\n
\t\t<td>".$r['start']."</td>\n 
\t\t<td>".$r['end']."</td>\n  

\t\t<td>".$r['timespan']."</td>\n 
\t</tr>\n\n"; 
    } 
    echo " 
</table>"; 


?>

[bpat1434]

I'd love to!

One question:
Where are you using it, and how are you using it?

[jumphopper]

well basically, my current code counts the amount of clicks on my flash site.
so when someone clicks a link, it keeps record and displays in a table, like this:
(id:1)(home=0) (start=1) (end=0) 2006-01-26
(id:2)(home=1) (start=0) (end=0) 2006-01-26

(id:3)(home=0) (start=1) (end=0) 2006-01-26

(id:4)(home=0) (start=0) (end=1) 2006-01-26

but, i want to have it so it displays all the clicks of a certain section of that day
in one row, like this instead:
(id:1)(home=23) (start=0) (end=0) 2006-01-26
(id:2)(home=0) (start=12) (end=0) 2006-01-26

from my research mysql_num_rows seems to be the solution to this... but i dont know where to input that command in my code.
let me know if i'm being unclear.

any ideas?

[bpat1434]

SELECT COUNT(home) AS c_home, COUNT(start) AS c_start, COUNT(end) AS c_end
FROM tracker
WHERE timespan LIKE '$year-$month-$day'

Then reference each as:
c_home = 23
c_start = 12
c_end = 0

So you'd say:

<?php
$query = "SELECT COUNT(home) AS c_home, COUNT(start) AS c_start, COUNT(end) AS c_end
FROM tracker
WHERE timespan LIKE '".$year."-".$month."-".$day."'";

$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
echo 'Home: '.$row['c_home'].'<br>Start: '.$row['c_start'].'<br>End: '.$row['c_end'];
?>

[jumphopper]

thanks! 🙂
but is there a way to count just the results inside the rows instead of the number of rows?
like this: [ http://i1.tinypic.com/mualog.jpg ]

[bpat1434]

<?php

$start = 0;
$home = 0;
$end = 0;

while($row = mysql_fetch_array($results))
{
  $start = ($row['start']>0)?$start+$row['start']:$start;
  $end = ($row['end']>0)?$end+$row['end']:$end;
  $home = ($row['home']>0)?$home+$row['home']:$home;
}

echo 'Start: '.$start.'<br>End: '.$end.'<br>Home: '.$home;
?>

[jumphopper]

it doesnt work 🙁 all i get is zeros.

[bpat1434]

Play aroudn with it....

[jumphopper]

am i suppose to replace my old count code?

<?php 
$query = "SELECT COUNT(home) AS c_home, COUNT(start) AS c_start, COUNT(end) AS c_end 
FROM tracker 
WHERE timespan LIKE '".$year."-".$month."-".$day."'"; 

$result = mysql_query($query) or die(mysql_error()); 
$row = mysql_fetch_array($result); 
echo 'Home: '.$row['c_home'].'<br>Start: '.$row['c_start'].'<br>End: '.$row['c_end']; 
?> 

am i on the right track?

 $start = 0; 
$home = 0; 
$end = 0; 

{ 
  $start = ($row['c_start']>0)?$start+$row['c_start']:$start; 
  $end = ($row['c_end']>0)?$end+$row['c_end']:$end; 
  $home = ($row['c_home']>0)?$home+$row['c_home']:$home; 
} 

echo 'Start: '.$start.'<br>End: '.$end.'<br>Home: '.$home; 

[bpat1434]

Yes, you should replace the old code with what I posted.

[jumphopper]

are there anymore hints you can give me, please? 😕

[jumphopper]

ive been playing with it but i still cant figure it out.... 🙁

[bpat1434]

No, you're not on the right track.... do this:

<?php

// Include database connection stuff here
$home = $start = $end = 0; // Default to 0

$query = "SELECT * FROM tracker WHERE timespan LIKE '".$year."-".$month."-".$day."'";
$result = mysql_query($query) or die('Oh My Gosh!!! mySQL threw an error!! Here you go!!<br>'.mysql_error());
while($row = mysql_fetch_array($result))
{
  $start = ($row['start']>0)?$start+$row['start']:$start;
  $end = ($row['end']>0)?$end+$row['end']:$end;
  $home = ($row['home']>0)?$home+$row['home']:$home;
}

echo 'Home: '.$home.'<br>End: '.$end.'<br>Start: '.$start;
?>