displaying number of records next to URL

[webguync]

Hello,

 

I am trying to figure out how something is done. Sometimes on websites such as job hunting sites, or  you will see a link at next to it a number in brackets or parentheses. example would be a link to open jobs for IBM followed by the number of IBM jobs available IBM(25). I know how to count records with SQL using count(), and I believe for each link you would need to store the records in a separate MySQL table, but am not sure how to go about displaying the results. Can anyone start me in the right direction or point me to a tutorial? I would do a Google search, but not sure what to search for 

[Psycho]

You would want two tables - one for the link types and another for the actual links.

 

Here are examples of two possible tables (I am only showing the most basic informationneeded here):

 

link_types:

id | name         
1    Construction
3    Fast Food

 

links:

id | type_id | Name
12     1       Contractor Jim
13     3       McDonalds
14     1       Bob's Electrical
15     3       Burger King
16     3       Wendy's

 

Now, if you wanted to creats links for each type just do a query such as:

SELECT link_typesname, COUNT(links.name) as count
FROM link_types
JOIN links ON link_types.id = links.type_id

 

You can then loop through the results to create a link to the "overview" page for each link type and include the number of items included.

[webguync]

thanks for the reply. I have added two MySQL tables. Would I then need something like this?

 

$result = @mysql_query($sql, $connection) or die (mysql_error());

while ($row = mysql_fetch_array($result))

 

 

and then what would I need to put in the echo statement to display the link with the number of records Fast Food(3)

<? echo  "<a href=$what_variable>$what_variable</a>"; ?>

 

[atomicrabbit]

well you would already know the link type that you're searching for because you would have needed to input that into the SQL SELECT statement as mjdamato wrote:

SELECT link_typesname...

where link_typesname was the type you were searching for.

 

So you would need to do something like this:

$type = "Fast Food"
$query = "SELECT '" . $type . "', COUNT(links.name) as count FROM link_types JOIN links ON link_types.id = links.type_id";
$result = @mysql_query($sql, $connection) or die (mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['count'];
echo  "<a href='http://www.im-not-sure-what-you-want-this-to-link-to.com'>$type($numrows)</a>";

 

and you could run that through a loop of whatever you want to search for. ie. loop through all the possible $type variables. Maybe you could make the $type variable an array, query the link_types table and input all the types into the $type var. THEN run the code I wrote above in a loop where it loops through all the values in the $type array.

 

I hope that made sense 

 

EDIT: I made a mistake in the code above -- fixed now 

[atomicrabbit]

sorry, disregard everything I wrote above... I think I'm VERY wrong.  :-\

[Psycho]

Well, I don't know exactly how you are doing the parent links. Do all the parent links go to the same page (but with different variable on the query string), or do they have a specific link that is entered in the link_types table? I will assume the first option:

 

$query = "SELECT link_types.id, link_types.name, COUNT(links.name) as count
          FROM link_types
          JOIN links ON link_types.id = links.type_id";

$result = mysql_query($query, $connection) or die (mysql_error());

while ($row = mysql_fetch_array($result)) {

    echo  "<a href=\"show_records.php?id={$row['id']}\">{$row['name']} ({$row['count']})</a>";

}

 

FYI: Why would you use the at symbol on the mysql_query() call if you are also going to include error handling with the die function? I would think that the at symbol (which suppresses errors) would cancel out the error handling.

[atomicrabbit]

$query = "SELECT link_types.id, link_types.name, COUNT(links.name) as count
          FROM link_types
          JOIN links ON link_types.id = links.type_id";

$result = mysql_query($query, $connection) or die (mysql_error());

while ($row = mysql_fetch_array($result)) {

    echo  "<a href=\"show_records.php?id={$row['id']}\">{$row['name']} ({$row['count']})</a>";

}

That's EXACTLY what was in my head, I just had a huge brain fart and wrote the exact WRONG thing.

[webguync]

thanks for the reply. I am getting a MySQL error."#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause "

 

I tried this, but get an error.

 

SELECT link_types.id, link_types.name, COUNT(links.name) as count

          FROM link_types GROUP BY link_types.name JOIN links ON link_types.id = links.type_id

 

is GROUP BY in the right place?

[sasa]

move Group BY near the end

[webguync]

I believe I have the SQL working now, but I am still getting an error on the page, and not sure where the syntax is off. Can anyone else tell?

 

<?php
// Connects to your Database
mysql_connect("localhost", "username", "pw") or die(mysql_error()) ;
mysql_select_db("bruceg_PublishingSystem") or die(mysql_error()) ;
$query = "SELECT link_types.id, link_types.name, COUNT(links.name) as count
          FROM link_types  JOIN links ON link_types.id = links.type_id GROUP BY  link_types.name ";

$result = mysql_query($query, $connection) or die (mysql_error());

while ($row = mysql_fetch_array($result)) {

    echo  "<a href=\"show_records.php?id={$row['id']}\">{$row['name']} ({$row['count']})</a>";

}

?>

[atomicrabbit]

what's the error?

[paul2463]

if you are getting a mysql_error then the reason is that "COUNT" is a mysql reserved word so your query syntax should be

$query = "SELECT link_types.id, link_types.name, COUNT(links.name) as `count`
          FROM link_types  JOIN links ON link_types.id = links.type_id GROUP BY  link_types.name ";

 

and also where does $connection come into it, you have already connected to the database in the lines before it

[webguync]

I am not sure the error, because I am testing on my ISP's server and they must have error reporting turned off in the phpini file, because I just get a blank white page. I tried adding into my code

 

  // Report all PHP errors

error_reporting(E_ALL);

 

but I still only get the blank page

 

I also meant to connect this way

 

$connection=mysql_connect("localhost", "bruceg_webmaster", "phoenix") or die(mysql_error()) ;
$db=mysql_select_db("bruceg_PublishingSystem") or die(mysql_error()) ;

[paul2463]

remove the $connection statement from you query call

<?php
// Connects to your Database
mysql_connect("localhost", "username", "pw") or die(mysql_error()) ;
mysql_select_db("bruceg_PublishingSystem") or die(mysql_error()) ;
$query = "SELECT link_types.id, link_types.name, COUNT(links.name) as `count`
          FROM link_types  JOIN links ON link_types.id = links.type_id GROUP BY  link_types.name ";

$result = mysql_query($query) or die (mysql_error());

while ($row = mysql_fetch_array($result)) {

    echo  "<a href=\"show_records.php?id={$row['id']}\">{$row['name']} ({$row['count']})</a>";

}

?>

[webguync]

ok, the script is working and with out errors now, thanks for all the help in getting me this far.

 

using the table examples given at the beginning of this thread I am getting a link for

 

Construction (2)

 

but not for fast food, and there are records there. Do I need to do a separate Query string for each category?

 

My test page is here:

http://www.inspired-evolution.com/Record_Count.php

 

[paul2463]

you don't need a second query for each category, that query is a good one and works, it found all the construction things for you, it seems a bit noddy but you are going to have to go into the database and make sure you have put the correct type_id in the links table because it should work

[paul2463]

one thing you could try, you could make it a LEFT JOIN, which will return all members of the first table even though they do not have anything in the second table, it may be worth a try to see if you get the link_types out

[webguync]

no, you were right the first time. I didn't have the type id matching up with the right category. Now, both categories are displaying.

 

http://www.inspired-evolution.com/Record_Count.php

 

one more thing. What would be the best way to display the results as a vertical list as opposed to horizontally as they are now?

 

I tried adding

 

echo "<ul><li>";
    echo  "<a href=\"show_records.php?id={$row['id']}\">{$row['name']} ({$row['count']})</a>";
echo "</li></ul>";

 

but that does not work

 

 

[paul2463]

just make it like this

echo  " - <a href=\"show_records.php?id={$row['id']}\">{$row['name']} ({$row['count']})</a></br>";

[webguync]

works great, thanks