PHP MYSQLI Count Row Correct

Question

Table

Hello i try to finds all hashtags with name #c3 and mysqli query show me: 3 rows but correct is 2 rows with #c3 ??? where is problem ? I want to show me 2Rows !

$sql_query = mysqli_query($Connection, "SELECT * FROM my_table WHERE hashtag LIKE '%#c3%'");
echo mysqli_num_rows($sql_query);

The problem lies in your not providing enough information; such as the hashtag values of the rows returned.... debugging 101. Note that `#c30` would match; `#c321` would match — Mark Baker, Aug 03 '16 at 12:00
Your query would match all rows that have `#c3` in `hashtag`, the `%` are wildcard characters. — segFault, Aug 03 '16 at 12:00
3 looks correct. Your `LIKE` expression is probably not what you want. — Marvin, Aug 03 '16 at 12:01
You are showing us 3 rows containing `#c3` so getting 3 rows is correct. You probably need another matching method if you want to match something more specific. — apokryfos, Aug 03 '16 at 12:01
all 3 records match your query. % is a wildcard, so you're asking for all records that contain #c3 anywhere, which the 3 records do match — Carl Casbolt, Aug 03 '16 at 12:01
`'%#c3%'` % after 3 will take all records matching after #c3 too. — Nana Partykar, Aug 03 '16 at 12:02
Normalize your column. CSVs are a bad layout for DBs. http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — chris85, Aug 03 '16 at 12:02
Solution is not to store anything delimited lists in a single column. Makes then almost impossible to query without lots and lots of jiggery pokery. Redesign or rather design you database again — RiggsFolly, Aug 03 '16 at 12:03

Funk Forty Niner · Accepted Answer · 2016-08-03T12:19:39.330

To answer this and as left in comments (by myself)

Use '%#c3' by removing the last %

Using LIKE, the % wildcard does the following and a few examples:

%XXX% - Matches anything before and after XXX

%XXX - Matches anything before XXX

XXX% - Matches anything after XXX.

References:

Plus, should there be any user input, consider using a prepared statement:

https://en.wikipedia.org/wiki/Prepared_statement

It will help against a potential SQL injection.

https://en.wikipedia.org/wiki/SQL_injection

score 0 · Answer 2 · answered Aug 03 '16 at 12:07

Hi Your query is wrong try to use this

SELECT * from my_table WHERE FIND_IN_SET('#c3', hashtag);

FIND_IN_SET returns the position of a string if it is present (as a substring) within a list of strings. The string list itself is a string contains substrings separated by ‘,’ (comma) character.

for more info read http://www.w3resource.com/mysql/string-functions/mysql-find_in_set-function.php

score 0 · Answer 3 · answered Aug 03 '16 at 12:07

0

You can use FIND_IN_SET

SELECT * FROM my_table WHERE FIND_IN_SET('#c3',hashtag)

See docs here and example here

answered Aug 03 '16 at 12:07

Tamil

1,193
9
24

score 0 · Answer 4 · answered Aug 03 '16 at 12:08

Split the table into 2 tables:

post: id | text | date
       1
       2
       3

post_hashtag: post_id | hashtag
                1         #c1
                1         #c3
                2         #c3
                3         #c3blabla

Then your query becomes:

SELECT post.*,post_hashtag.hashtag FROM post JOIN post_hashtag ON post.id=post_hashtag.post_id WHERE hashtag='#c3#';

The act of splitting the table is called normalizing and you need to do this because your tables are not in any normal form which makes them not relational which defeats the purpose of storing them in a relational database. Check https://en.wikipedia.org/wiki/First_normal_form for more information.

score 0 · Answer 5 · answered Aug 03 '16 at 12:09

Looking at your table, I would suggest that you break up your tables to have a many to many link for hashtag. That way you can search for all records that have a matching hashtag.

data table
+----+------+------+
| id | text | date |
+----+------+------+

hash table
+----+------+
| id | hash |
+----+------+

link table
+---------+---------+
| data_id | hash_id |
+---------+---------+

This would then allow you to use an SQL statement like:

SELECT * FROM data
INNER JOIN link ON data_id = data.id
INNER JOIN hash ON hash_id = hash.id
WHERE hash = '#3';

PHP MYSQLI Count Row Correct

5 Answers5