-1

So to my knowledge I have a pretty simple piece of php that has been breaking my back all day

  $startDate = "2016-07-27";
  $endDate = "2016-07-31";

  //--------------------------------------------------------------------------
  // 1) Connect to mysql database
  //--------------------------------------------------------------------------

  $con = mysql_connect($host,$user,$pass);
  $dbs = mysql_select_db($databaseName, $con);

  //--------------------------------------------------------------------------
  // 2) Query database for data
  //--------------------------------------------------------------------------
  $result = mysql_query("SELECT * FROM publishersStatistics WHERE dataDate BETWEEN STR_TO_DATE('" . $startDate . "','%Y-%m-%d') AND STR_TO_DATE('" . $endDate . "','%Y-%m-%d') ORDER by dataDate DESC");

  $array = mysql_fetch_row($result);                          //fetch result    

  //--------------------------------------------------------------------------
  // 3) echo result as json 
  //--------------------------------------------------------------------------
  echo json_encode($array);

The code is very simple and this is the entire document (other than database connection stuff) - It takes a start date and end date and finds all values in-between those two dates. However for some reason it's only returning the last date in the query (2016-07-31)'s information.

I have no idea why this is!

I have also tested using ASC instead of DESC, this returns the first result. (2016-07-28)'s information only! Does anyone have any idea why I seem to be able to only recall one row?

Thanks in advance!

ashlewis
  • 3
  • 3
  • 2
    `mysql_fetch_row` fetches __row__ and not __all rows__ – u_mulder Aug 02 '16 at 18:25
  • **Stop** usind deprecated `mysql_*` API. Use `mysqli_*` or `PDO` with prepared statements – Jens Aug 02 '16 at 18:25
  • Please [stop using the mysql_ functions as they have been removed from PHP](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) – Machavity Aug 02 '16 at 18:25
  • Thanks! I've changed to mysqli_, is there a way to recall all rows that match the between statement in that case? Thanks again. – ashlewis Aug 02 '16 at 18:27
  • $rows = mysqli_fetch_assoc($result); – coderodour Aug 02 '16 at 18:28
  • @ashlewis please take a look on first comment it's your answer. u_mulder is right. – milan kyada Aug 02 '16 at 18:29
  • so...... do a *loop dah loop!* `while` you're at it. ;-) and *enjoy the ride!* – Funk Forty Niner Aug 02 '16 at 18:30
  • @kooKooDoneSlapHisWife `mysqli_fetch_assoc()` - the OP is using the MySQL_ API and not the MySQLi_ one, which are not compatible together. – Funk Forty Niner Aug 02 '16 at 18:35
  • @Fred-ii- The OP commented that they had changed it to msqli... – coderodour Aug 02 '16 at 18:39
  • @kooKooDoneSlapHisWife I see the comment now; you're right. The OP needs to update their code in order to reflect what they are `NOW()` using. I smell "mixing MySQL APIs" at this point in time and/or probably still not looping over successful results. – Funk Forty Niner Aug 02 '16 at 18:41
  • There isn't enough code here to support the question, especially about the `ajax` tag and what is now being used, `mysqli_`. Remember this: The LESS we know, the MORE TIME it takes to provide you with a solution. I for one have hung around here long enough. See the answers given below. – Funk Forty Niner Aug 02 '16 at 18:46

1 Answers1

0

mysql_fetch_row() return one row , you can loop results by using

A) mysql_fetch_array() return array(0=>value,column=>value);

or

B) mysql_fetch_assoc() return array(column=>value) as you need in json..

while($row = mysql_fetch_assoc($result)){
$array[] = $row;
}
 echo json_encode($array);

mysql is old and now all walk to mysqli

Homam Alhaytham
  • 115
  • 6
  • Did you read over the (new) comments under their question? Especially the one about: *"Thanks! I've changed to **mysqli_,** is there a way to recall all rows that match the between statement in that case? Thanks again. – ashlewis 15 mins ago"* – Funk Forty Niner Aug 02 '16 at 18:44
  • This works fine using mysql! Thanks :) I've changed to mysqli and that seems to be working either way but thanks for providing the mysql version just incase! – ashlewis Aug 02 '16 at 18:46