mysql

[davy_yg]

Hello,

I am a mysql table called:

adsmgt which consists of: idads and time

I would like to count the number of row in adsmgt how to do so?

I try this:

mysql_select("select TABLE admgt count(idads)");

This error appears: (it doesn't matter if the idads are twins they still have to be counted as two distinct row)

Fatal error: Call to undefined function mysql_select() in C:\xampp\htdocs\Innovation\script_shop\adsmgt.php on line 34

[jkon]

Hello davy_yg,
I guess that you aren’t a MySql table as you said but a human ( ;)) ) so try:

SELECT COUNT(idads) AS COUNTER FROM admgt  

About the mysql_select problem you are facing you have to share your code to give you advices. ( I am using PDO but what function is the mysql_select ? )

@davy

for somebody who's been kicking around with php/mysql for a considerable time, I'm surprised that you haven't found the php manual and mysql manuals yet:

http://php.net/manual/en/function.mysql-query.php

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count

[davy_yg]

mysql_select("SELECT Count(idads) AS COUNTER FROM adsmgt");

// ads management script
Fatal error: Call to undefined function mysql_select() in C:\xampp\htdocs\Innovation\script_shop\adsmgt.php on line 34

use mysql_query as advised already.

[davy_yg]

$result = mysql_query("SELECT Count(*) FROM adsmgt") or die(mysql_error());

echo $result;

Resource id #4

Why is it? I'm looking for a number (as a result of count(*))

[pritaeas]

You need to use mysql_fetch_array($result) first. You can't just use the returned resource.

[davy_yg]

$result = mysql_query("SELECT Count(idads) FROM adsmgt") or die(mysql_error()); 

while($row = mysql_fetch_array($result))
{
echo $row['count(idads)'];

}

Notice: Undefined index: count(idads) in C:\xampp\htdocs\Innovation\script_shop\adsmgt.php on line 38

[bops]

$result = mysql_query("SELECT COUNT(`idads`) FROM `adsmgt`;");
$row = mysql_fetch_array($result);
echo $row[0];

mysql_fetch_array() does not return an associative array, it returns a numeric indexed array. If you want to get something by it's field name i.e. return an associative array use mysql_fetch_assoc(). I would recommend using SELECT COUNT(idads) AS countidads FROM adsmgt; and then use countidads to reference it when using mysql_fetch_assoc().

The fetch_array gets two lots of results - index-based and numeric-based arrays - by default. Decide which one you want. Anyway the above will work or if you need an association:

$result = mysql_query("SELECT COUNT(`idads`) AS idads FROM `adsmgt`;");
$row = mysql_fetch_assoc($result);
echo $row['idads'];

However, you can limit the fetch_array to one type of array:

$result = mysql_query("SELECT COUNT(`idads`) FROM `adsmgt`;");
$row = mysql_fetch_array($result, MYSQL_NUM); //or MYSQL_ASSOC - default is MYSQL_BOTH
echo $row[0];

[davy_yg]

scriptlist.php

<a href="adsmgt.php?idads=1"><img src="../images/Advertisement.jpg"></a>

adsmgt.php

$count = 0;
if ($_GET['idads']==1)
{
$count++;

mysql_query("insert into adsmgt (idads,waktu) values ('idads','time()')");
}

The ads counter already works (the script is not listed) - it just the values of idads and time are all 0. idads only to show which ads is counted. Two different ads has to have two different ids.

@davy_yg

The ads counter already works (the script is not listed) - it just the values of idads and time are all 0. idads only to show which ads is counted. Two different ads has to have two different ids.

What is the issue now? Can you explain more? Do you want to select those 2 different ids? Is this the same script that veedeoo posted from the other thread?

[davy_yg]

Well in this case, let say I only have one ads. Next time maybe more.

@davy_yg

Well in this case, let say I only have one ads. Next time maybe more.

So each ads has an id?

Try this example:

SELECT * FROM adsmgt WHERE id IN (1, 2, 3, 4, 5)

This will select the id that has ads from adsmgt

It's getting late I gotta go

[davy_yg]

My question is : eventhough the number of row is being added in the database why the values of idads and time are all 0 ?

You need to show your code again, to see what you've changed. Include your table structure.

[davy_yg]

here is my whole code:

adsmgt.php

<?php

$con = mysql_connect("localhost","root","");

mysql_select_db("freescript", $con);

// add number of clicks

$count = 0;
if ($_GET['idads']==1)
{
$count++;

$time = time();
$idads = 1;

mysql_query("insert into adsmgt (idads,waktu) values ('$idads', '$time')");
}

// number of clicks

$result = mysql_query("SELECT COUNT(idads) AS countidads FROM adsmgt") or die(mysql_error()); 

while($row = mysql_fetch_assoc($result))
{
echo "Number of ad clicks: ".$row['countidads'];

}

My database:

TABLE: freescript

Column: idads waktu number

[AndreRet]

a Few things I've picked up...

You have 3 fields in your table freescript - idads, waktu, number. When you insert a record, you do not specify anything for number which will result in a NULL entry.

mysql_query("insert into adsmgt (idads, waktu, number) values ('$idads', '$time', '')");

When you echo out the value of your id's, you need to use the field name...

echo "Number of ad clicks: ".$row['idads'];

If you only want to show how many records there are (which was your original question)...

$result = mysql_query("SELECT * FROM adsmgt");
$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";

If you want to show how many records (clicks) for a certain user...

//Assuming you have user id (idads) in memory already and you have it assigned to variable called $user_id...

$result = mysql_query("SELECT * FROM adsmgt WHERE idads='$user_id'");
    $num_rows = mysql_num_rows($result);

    echo "$num_rows Rows\n";

[davy_yg]

When you insert a record, you do not specify anything for number which will result in a NULL entry.

Does this not count as values?

$time = time();
$idads = 1;

waktu: datetime
idads: int(3)

// this time I use number for idads - next time maybe I can change it to something else like $user_id

[AndreRet]

Yes they do count as values. I was refering to your field "number" in your table freescript which did not get assigned a value.

Not sure what you mean by number for idads, next time maybe $user_id. :)

Did my above code help with your question?

[davy_yg]

I set number as my primary key and auto_increment. Number yes has a value. I just wonder why the rest remains 0.

[AndreRet]

I see. That makes more sense. That is however a whole new question. Your original question was how to return the rows, not why does your data not update correctly.

Your original question has been answered here. Please open a new thread with your question on how to update your records.

[gary.kline.940]

Wrote this response, then realised that OP should open a new thread. Well, here's an answer for his last question. If davy has another question he should start another thread.

One problem I see is with this insert statement:
mysql_query("insert into adsmgt (idads,waktu) values ('$idads', '$time')");

The double quotes convert the variables to values, leaving the following query (as an example)
insert into adsmgt (idads,waktu) values ('1', '1321321654981321321') <<< WRONG >>>

As such, your SQL statement is trying to insert string values into a number and datetime fields. This results in a 0 value in the idads and waktu fields.

The statement should instead read:
mysql_query("insert into adsmgt (idads,waktu) values ($idads, $time)");

You're also trying to insert a Unix timestamp into a date-time field, which won't work. You should set the $time variable something like below, so the $time variable holds a formatted date string:

$time = date('Y-m-d H:i:s');

If $time is set to a string value, your insert statement then becomes:
mysql_query("insert into adsmgt (idads,waktu) values ($idads, '$time')");

[pritaeas]

The double quotes convert the variables to values, leaving the following query (as an example)
insert into adsmgt (idads,waktu) values ('1', '1321321654981321321') <<< WRONG >>>

I code queries like you suggest, but your statement is not true. MySQL will correctly convert '1' into 1.

[yehuda2001]

Not sure why everybody is making this so complicated, it's simple. IF I have a table called "records" and the first column is called "id" (as should be on every databse table), then I'd grab the ID from each row (fastest mysql query possible, since it's a small result) and then count the number of rown collected.

$qry = mysql_query('SELECT id FROM records');
$recordCount = mysql_num_rows($qry);

The value of $recordCount is the number of rows you have selected. You can add as many WHERE variables or ORDER BY as you want, but the important part is the "mysql_num_rows" to count how many results there are.

I would disagree with the mysql_num_rows method being faster than COUNT(), eg:

http://stackoverflow.com/questions/2485224/sql-php-which-is-faster-mysql-num-rows-or-select-count