Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have database "db2" with table "menjava"

In table menjava have "id", "author" and "date_submitted" field

  • id - auto_increment
  • author - int(11)
  • date_submitted - datetime

I want to count all the rows for todays date and all the rows for yesterdays date (so there will be two codes with conditions) based on a DATETIME field called 'date_submitted' that holds the date and time of each record's creation.

In the file result.php, there is this count displayed, but it does not work. In the same file (result.php) I have some other code to display data from different database, so I think that povezava.php is working ok.

My code:

 <?
    require "povezava.php";
    $q=mysql_query(" SELECT COUNT(*) AS total_number FROM menjava 
WHERE date_submitted >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)",$link2);
// now you can 
if ( $nt = mysql_fetch_array($q)){
echo $nt["total_number"];
$q=mysql_query($nt) or die(mysql_error());
}

    ?>

my file povezava.php looks like this:

<?
$servername='localhost';

$dbusername='user';
$dbpassword='pass';

$dbname1='db1';
$dbname2='db2';

$link1 = connecttodb($servername,$dbname1,$dbusername,$dbpassword);
$link2 = connecttodb($servername,$dbname2,$dbusername,$dbpassword);

function connecttodb($servername,$dbname,$dbusername,$dbpassword)
{
    $link=mysql_connect ("$servername","$dbusername","$dbpassword",TRUE);
    if(!$link){die("Could not connect to MySQL");}
    mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
    return $link;
}
    ?>

Error that I get:

A PHP Error was encountered

Severity: NoticeMessage: Array to string conversionFilename: templates/master.phpLine Number: 231 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Array' at line 1

Fixed:

<?
    require "povezava.php";
    $q=mysql_query("SELECT COUNT(*) AS total_number FROM menjava WHERE date_submitted >= DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY)",$link2);
// working 
if ( $nt = mysql_fetch_array($q)){
echo $nt["total_number"];
}

    ?>

Thank you!

share|improve this question
add comment

1 Answer 1

up vote 0 down vote accepted

You need to access column by index :

if ( $nt = mysql_fetch_array($q)){
echo $nt[0];
}

(Note if instead of while - it's not required, but your query returns at most 1 row, so no need for while).
If you want to access column by name, change the query to use aliases:

$q=mysql_query(" SELECT COUNT(*) AS total_number FROM menjava 
WHERE date_submitted >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)",$link2);
// now you can 
if ( $nt = mysql_fetch_array($q)){
echo $nt["total_number"];
}

Also, while debugging it makes sense to output errors if any :

$q=mysql_query(....) or die(mysql_error());
share|improve this answer
    
I get this error: Severity: WarningMessage: mysql_fetch_array() expects parameter 1 to be resource, boolean givenFilename: result.php –  Sašo Krajnc Dec 26 '11 at 13:57
    
So the query fails (the most likely because it shouldn't be a space between COUNT and (*) : SELECT COUNT(*) ... –  a1ex07 Dec 26 '11 at 14:16
    
ok..it is working.. I get count for today and yesterday but still there is the same error: Severity: WarningMessage: mysql_query() expects parameter 1 to be string, array givenFilename: result.phpLine Number: 231 how can I hide this or what can I do? –  Sašo Krajnc Dec 26 '11 at 14:44
    
Can you post the code that produces error ? You pass array instead of string somewhere. –  a1ex07 Dec 26 '11 at 14:49
    
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Array' at line 1 –  Sašo Krajnc Dec 26 '11 at 14:49
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.