Select count from .txt file

[Tasos]

Hello there,

 

This is the script i use right know, and i want it to become easier because i have so much categories that i want to show and it will take days to write all those categories.

<?php
include 'extern/connect-.php';
//*****************************************************************************
// categEGORY A
//*****************************************************************************
$result = mysql_query("select 
(select count(1) FROM videos WHERE title LIKE '%Accident%') as Accident	,
(select count(1) FROM videos WHERE title LIKE '%Acrobatic%') as Acrobatic,
(select count(1) FROM videos WHERE title LIKE '%Adorable%') as Adorable	,
(select count(1) FROM videos WHERE title LIKE '%Adventure%') as Adventure,

");
                                                            
$row = mysql_fetch_assoc($result);
 
foreach($row as $title => $total)
{
  echo '
<div id="categ">
<div class="a">
<a href="search.php?search='. $title . '&submit= ">'. $title.'  '. $total .'</a></div></div>';
}

?>

So what i was thinking is if it is possible to make .txt file and to select from it like this here below, or if something else could help make the categories faster to write and get the best performance to load the webpage... 

 

 

some_file.txt

1. Accident    
2. Acrobatic
3. Adorable    
4. Adventure

<?php include 'extern/connect-.php';

$something ='some_file.txt';


//*****************************************************************************
// categEGORY A
//*****************************************************************************
$result = mysql_query("select 
(select count(1) FROM videos WHERE $something  ......?	,


echo 'categories';
?>

Any help is Appreciated

Thanks in Advance

[mac_gyver]

you should store the category names in a database table along with an auto-increment id column, that becomes the category_id. you then need to add a category_id column to your videos table and store the correct category_id in it.

 

once you have the category_id column in your videos database table, you would simply GROUP BY category - 

select count(1) as count FROM videos GROUP BY category_id

to get the correct category name, you would write a JOIN query between the videos table and the category table using the category_id column in the two tables.

[ginerjm]

What mac_gyver says is true but.....

 

Looking at your category values  I find it hard to call your database "categorized" at all.  The values you have in the 'title'' column are certainly very disjoint and do not make for good analysis of your data in meaningful groupings.  I mean you have "accident", "adorable" and "adventure".  What do there have in common that they should be values of a single column?  It is beginning to look like a dictionary of simple words which as you already stated will take days to comprehend and make sense out ot.

 

Perhaps you need to re-think your database structure.  Or maybe give us an idea of what you are doing.

 

As an example of what I mean how will you treat a record that has a title of "accidental adventure" or "adorable acrobat"?

Edited September 19, 2014 by ginerjm

[Tasos]

What i try to make i a video sharing website but simple, i was watching at a website a long time ago and i forgot wich one that is with videos also, They had at the top bottom a list like 

 

# A B C D and so on. and that is what i also want at the top bottom with all the categories i want to show.

 

And actualy i dont know how to do that simple fast and smart, i founded a long time ago a script that allow me to echo all the tags from database but that was not  nice and how i want to show it, that script shows it like this here below

 

Ad

asfs

Adventure

acro

acrob

acrobatic

aer

aerob

aerobics

 

and so on, so what i whas thinking is how to make it good looking with complete words and to show what i want exactly in the categories list.

 

i hope I'm understanding 

Thanks in Advance.

[QuickOldCar]

What the other guys said is true.

 

My suggestion would be to use a full-text search, even install sphinx search

 

It's silly trying to categorize every single word and also multiple words in any combination.

 

Instead you can make some search terms that are popular and provide some links in a sidebar or something.

 

Then also have the ability to search anything else.

 

Onto one of your questions as to how to make a letter and number navigation...

$characters_array = array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z");

foreach ($characters_array as $characters) {
            $upper_char = strtoupper($characters);
            echo " <a href='yourscript.php?search=".$characters."'>[".$upper_char."]</a> ";
        }

if(isset($_GET['search']) && trim($_GET['search']) != ''){
$search = strtolower(trim($_GET['search']));

//then some sort of query matching results by just the first character
$query = "SELECT * from videos WHERE title LIKE '". mysql_real_escape_string($search) ."%'";
}

I didn't bother doing the database connection or while loop, you should really not be using any mysql_* functions but using PDO prepared statements or mysqli_* functions

 

If collation of your column is set as _ci, that means is case insensitive meaning a and A will work the same in the LIKE and return both sets of results.

If is not set to that you need to run 2 like statements for uppercase and lowercase...or change your column collation to _ci

Edited September 20, 2014 by QuickOldCar