What built in functions should I use?

[pahunrepublic]

Hi everyone. Good day for coding?

I created this tiny script just for self-learning purposes but I'd like to improve it.

Here is the script:

<?php
include_once 'dbinfo.php';
if(isset($_POST['submit'])) {
$label =	$_POST['label'];
$stm = $connect->prepare("INSERT INTO words(word) VALUES (?)");//$stm = $connect->prepare("INSERT INTO words VALUES (?,?)");
$stm->bind_param('s', $label);//$stm->bind_param('ss', $label, $user);http://php.net/manual/en/mysqli-stmt.bind-param.php
$stm->execute();
printf("%d Label inserted.\n", $stmt->affected_rows);
$stm->close();
$connect->close();

}
?>
<form action="" name="submit_label" method="POST">
<p>Submit a Label:<input type="text" name="label" value=""></p>
<input name="submit" type="submit" value="Submit label">
<p></p>
<a href="wordlist.php" >The List of labels</a>
</form>
<?php if ($stm = $connect->prepare("SELECT word, label_id FROM words ORDER BY label_id")){
$stm->execute();
$stm->bind_result($labelcol, $labelcol2);
while($stm->fetch()){
printf("%s %s</br>", $labelcol, $labelcol2);	
}
$stm->close();
}
$connect->close();
?>

It submits words and insert them in a table called words. It also outputs the table content, the submitted words.

An image of a database structure is attached to this post.

What I'd like to do is to count the how many times the same words are repeatedly submitted and output the the repeated word next to the number of times it is repeated.

 

For example:

user 1 submitted 'awesome' 3 times so it shows awesome (3)

user2 submitted 'bad' 45 times so it shows bad (45).... and so on

 

I'm guessing that I should use arrays and and built in functions and things like that but my question which functions should I use to get started or just give me some hint how to use arrays in this case.

 

Thank you very much in advance.

 

 

 

 

[attachment deleted by admin]

[gizmola]

Not really, these types of counts can be done by the database using the GROUP BY.  First off, there is nothing in your database that relates a word to a user.  So there is no way you're going to get a count by user at present.  If you did the principle would be the same, although user_id would be part of the GROUP BY.

 

Ignoring that you can get a count of repeated words with this query:

 

SELECT words, count(*) as countof
FROM labels
GROUP BY words
order by countof DESC

 

 

[pahunrepublic]

...you can get a count of repeated words with this query

SELECT words, count(*) as countof
FROM labels
GROUP BY words
order by countof DESC

Yes this is what I wanted. Thank you gizmola. I never thought this should be resolved with MySQL query. I have some questions. I try to interpret this SQL query but I couldn't find this 'countof'. What does it do? Which part of the query counts the rows in the table? Any tutorial on this topic?

 

 

[MasterACE14]

'countof' is simply an alias for the value that count(*) returns.

[pahunrepublic]

So It would be interpreted like this?: "select and count all the rows from words column in labels table ..."

SELECT words, count(*) as countof

FROM labels

GROUP BY words

order by countof DESC

[jcbones]

It would be interpreted as,

 

Select distinct words, and a count of how many times the word exist from labels, return the results by the count (highest to lowest).

 

The returned columns would be 'words' and 'countof'

[pahunrepublic]

tahnx