how to echo from database

[Tasos]

Hello, i have a question.

 

I've recently started a website with videos, and I was looking for something like this.

 

http://video.xnxx.com/tags/ . But i want to make a list with a to z.

 

sorry for the link but this is basically what i'm looking for. ( My website is not based on porn )

so how to echo all the list form my table in the database.

 

From videos Where title LIKE '%Funny%' as Funny

 

 

Thanks.

Edited April 9, 2013 by Tasos

[requinix]

Dude. I'm sure there's just a good example somewhere out there that isn't related to porn. And next time spell out that you're linking to something NSFW.

 

Want a page that lists all the tags? Then do that. Use a query like

SELECT t.name, COUNT(*) AS count
FROM tags t
JOIN tags_videos tv ON t.id = tv.tag
GROUP BY t.name

[Tasos]

videos = database.

title = in videos.

SELECT title, COUNT(*) AS count
FROM videos
JOINT title_videos tv ON id = tv.tag
GROUP BY title  

Is this corect ?

[requinix]

Does it work?

[Q695]

it's a mysql sort by, not a php issue.

[Tasos]

I have this, 

<?php
include 'extern/connectsearch.php';

$query = "SELECT title, COUNT(*) AS count
FROM videos
JOIN videos ON title
GROUP BY title";

 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
	echo $row['title'];
	echo "<br />";
}
mysql_close($connect);
?>

I dont think that this is good, beacuse this is what i get  

 

Not unique table/alias: 'videos'

 

Myn database called main-videos and the table is videos and i want to get from videos is title and to echo all the list like:

 

funny 5000

cars 3000

bikes 1500

crazy 5.000.000  

 

i have this script but, i have more than 20k videos and other linked websites so if i have to made it by hand than    to much. haha

<?php
include 'extern/connectsearch.php';
 
$result = mysql_query("select (select count(1) FROM videos WHERE title LIKE '%Funny%') as Funny,
                              (select count(1) FROM videos WHERE title LIKE '%Cars%') as Cars,
                              (select count(1) FROM videos WHERE title LIKE '%Bikes%') as Bikes,
                              (select count(1) FROM videos WHERE title LIKE '%Crazy%') as Crazy");
$row = mysql_fetch_assoc($result);
 
foreach($row as $title => $total)
{
  echo '<div class="textbl">' . $title . ': ' . $total .'</div>';
}
 
mysql_close($connect);
?>

Edited April 10, 2013 by Tasos

[requinix]

So what happened to the SQL you posted a bit earlier? That was probably going to pull video titles instead of tags but it was really close - why'd you change it?

[Tasos]

because it did not work, and when i made the change i get Not unique table/alias: 'videos' something showed up.

 

 

i dont want to show id, only title and the number of  how much videos '%Funny%' has like,

 

Funny 5000 

 

This is my database ( test = database ( videos = tabel ( title = in table videos )

 

SELECT title, COUNT(*) AS count
FROM videos
JOIN title_videos tv ON id = tv.tag
GROUP BY title

 

this won't work because i dont want to show id, i want to show only tags/title and how many there are.

[requinix]

Close as in close, not as in it does exactly what you want.

 

You're SELECTing and GROUPing BY the title. Presumably the video title. That's not what you want. You want the tag name. So SELECT and GROUP BY the tag name.