displaying the number of results in mysql database

[ckwall]

I am capturing urls with variables into my database.
I would like to have a page that displays the number of times I have logged a particular url into my database.

Currently I am displaying each record. So if I have http://url1.com in my database 10 times, I show 10 individual listings of http://url1.com.

What I would like to see is something to the affect of:
http://url1.com 10
http://url2.com 5
http://url3.com 7

You know what I mean?

I think that the way I want to do that is to use looping. I just dont know the correct syntax. I can do a loop to echo something over and over, but that doesnt do me any good here.

Can anyone help me with this?

Thanks in advance
Chris

[snoopy13]

you could use
mysql_num_rows () to see how many times a url is in your db.
eg.
$query="SELECT url FROM x WHERE url='http://url1.com'";
$number=mysql_num_rows($query);
echo $number;

[ckwall]

Is there a way to do this without specifying what the url value should be = to?

for example, if there is a new url logged that I am not expecting, how can I caputure that?

[ckwall]

Also, I am getting an error.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\program files\web\easyphp\www\in_progress\survey\stats.php on line 46

I am trying to use the statement you gave me with my own values. I am using it in a different application, becuase I think it will help me with that also.

Here is my script:
<?
//Database user info
$username="root";
$password="";
$database="survey";

//Connect to database
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

//Display values from form field 1
$query="SELECT method_of_communication FROM results WHERE method_of_communication='email only'";
$number=mysql_num_rows($query);
echo $number;

//print errors if any on page feild 1
echo mysql_error();

//disconnect from the database
mysql_close();
?>

[snoopy13]

In that case I would suggest to do it differently
you could, when a url is put in, first look in the database if it already exists and if, update the number

'times' 'url'
$query=mysql_query("SELECT url FROM table WHERE url='$url'");

$count=mysql_num_rows($query);
if ($count>0){
$update="UPDATE url SET number='$x'"
}
else{
$insert="INSERT INTO table (url, times )VALUES ('$url', '1');
}

something like this

[snoopy13]

maybe you'd better say
$query=mysql_query("SELECT method_of_communication FROM results WHERE method_of_communication='email only'";
$number=mysql_num_rows($query);
echo $number;

does is work now?

[ckwall]

Parse error: parse error, unexpected ';' in c:\program files\web\easyphp\www\in_progress\survey\stats.php on line 45

[snoopy13]

so what is in line 45? maybe because you (as I ) forgot to close)

[ckwall]

<?
//Database user info
$username="root";
$password="";
$database="survey";

//Connect to database
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

//Display values from form field 1
$query=mysql_query("SELECT method_of_communication FROM results WHERE method_of_communication='email only'";
$number=mysql_num_rows($query);
echo $number;

//print errors if any on page feild 1
echo mysql_error();

//disconnect from the database
mysql_close();
?>

Line 45 is $query=mysql_query("SELECT method_of_communication FROM results WHERE method_of_communication='email only'";

[ckwall]

ohhh... I see it

the close ")"

thanks for that.

[ckwall]

Can you help me understand how this one works?

In that case I would suggest to do it differently
you could, when a url is put in, first look in the database if it already exists and if, update the number

'times' 'url'
$query=mysql_query("SELECT url FROM table WHERE url='$url'");

$count=mysql_num_rows($query);
if ($count>0){
$update="UPDATE url SET number='$x'"
}
else{
$insert="INSERT INTO table (url, times )VALUES ('$url', '1');
}

something like

[ckwall]

I am confused on line 1. what are these supposed to be doing?

'times' 'url'

Line 2, where does the $url variable come from?
$query=mysql_query("SELECT url FROM table WHERE url='$url'");

$count=mysql_num_rows($query);
if ($count>0){

Line 5 where does the $x variable come from?
$update="UPDATE url SET number='$x'"

}
else{
$insert="INSERT INTO table (url, times )VALUES ('$url', '1');
}

[ckwall]

OK, now I am completely lost.

Let me start fresh.

I am logging the variable id= into my database.

with that, and whatever else may be required, I would like to have a page that displays all of the unique id's that have been logged, and how many times they have been logged.

I also do not want to alter my code every time a new id is assigned. I would like to dynamically log and display each id as it is captured.

I did not understand the above script. I am as you can see by the category and my level of ineptness to php... a newbie.

Please reply accordingly. I appreciate any help that can be provided. Thank you.

Chris

[drag0n]

I think snoopy's idea to do it is correct
When you want to log a url into the db, first check if it is in there, if it is, just update the db add one to "hits" or whatever, and exit;
and if it isnt in there, add a new row with the url being put in and set "hits" or whatever column to 1 and it begins all over again, do you know what I mean?

[ckwall]

I understand the concept 100%. What I am struggling with is the syntax. I have never done anything like this before. I tried the syntax that snoopy13 suggested. But It didnt work. From what I understand about php thus far, there is still a ton missing from the script.

I am confused on line 1. what are these supposed to be doing?

'times' 'url'

Line 2, where does the $url variable come from?
$query=mysql_query("SELECT url FROM table WHERE url='$url'");

$count=mysql_num_rows($query);
if ($count>0){

Line 5 where does the $x variable come from?
$update="UPDATE url SET number='$x'"

}
else{
$insert="INSERT INTO table (url, times )VALUES ('$url', '1');
}

[snoopy13]

good morning - I'm back again
'times' and 'url' are meant as colums in the database

the $url variable comes from the form or wherever you let someone insert the url (I don't know what your script is for, so I don't exactly know where you get the variable from)

the '$x' variable you make yourself...first you read out the last 'times' from the database and then add +1 and write that into the $x

ok?

[ckwall]

ok.. somewhat less confusing but still not clear.

Let me tell you what I am doing:

Here is the script to capture the url variable.

<?php
if ($_GET['id']) {
echo "<input type='hidden' name='id' value='".$_GET['id']."'>";
}
?>

<?
//Database user info
$username="******";
$password="******";
$database="******";

//Connect to database
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

//Insert values from url variable
$query = "INSERT INTO who (url) VALUES ('$id')";

//run query variable
mysql_query($query);

//print errors if any on page
echo mysql_error();

//disconnect from the database
mysql_close();
?>

[snoopy13]

you should start looking if this url has already been inserted into the database
did I understand you right - you get the url in the variable '$id'? so this is what you do:

//connect to database........then:
// the variable x should always add one (but I'm not quite shure if this is the right code)
$x=+1
//make a query to find out, if the url is already in the database
$sql=mysql_query("SELECT times FROM who WHERE url='$id'"); 
if (!$sql) echo "didn't work";
$count=mysql_num_rows($sql);
if ($count<1){
//if not, insert a new row with the values from the variable id and 1 (because it's the first time to insert this url)
$sql=mysql_query("INSERT INTO who (url, times) VALUES ('$id', '1')");
if (!$sql) echo "didn't work";
}
//if this url is already listed in the db, just update it, making the 'times' one more
else{
$query=mysql_query("UPDATE who SET times='$x' WHERE url='$id'");
if (!$sql) echo "didn't work";
}

//and then you can echo the whole thing, disconnect and stuff

[snoopy13]

the thing with the ad 1 doesn't work like I told you..but you could do the fallowing to resolve that problem:

//read out the number of times 
$sql=mysql_query("SELECT times FROM who WHERE url='$id'");
if(!$sql)echo"didn't work";
$query=mysql_fetch_array($sql);
//and because you know that each url has only got one row, you can say to take the first out of the array '$query' and then ad one and make it variable '$x'
$x=$query[0]+1;
$sql=mysql_query("UPDATE who SET times='$x' WHERE url='$id'");
if(!$sql)echo"didn't work";

that should do

[ckwall]

ok, so to show the details, what would I echo if all i wanted to show was the url variable that I captured, and how many times it had been captured?