stkmedia stkmedia - 1 month ago 14
MySQL Question

select rows where same column values start with string, 2 questions

I'm trying to get multiple sums of rows, each sum has a matching column string value, and all the values have a matching 4 character prefix, but it's not working and I could use some help. Also, would CASE be a more or less costly query on the db?
Started with this:

$sql =
"SELECT col1
FROM table
WHERE substr(col1,1,5)='$string'";
$query = mysqli_query($con, $sql);
$row = mysqli_fetch_array($query,MYSQLI_ASSOC);
$results = $row['col1'];
$sum1 = count(array_keys($results,'string1'));
$sum2 = count(array_keys($results,'string2'));


Does it work to get the results from the same column that's in the WHERE clause?

In practice, col1 has row values like aaaa_string1, aaaa_string1, aaaa_string2, aaaa_string2, bbbb_string8... so I'm looking to get all col1 results that have the aaaa in an array, then subsequently filter how many exist of each string1 and string2.

Answer

Use SUBSTR(col1, LENGTH('$string')+1) to get the part of the column after the prefix, and group by this.

Use LIKE 'prefix%' to match a column beginning with a prefix.

SELECT SUBSTR(col1, LENGTH('$string')+1) AS suffix, COUNT(*) as count
FROM table
WHERE col1 LIKE '$string%'
GROUP BY suffix

Then you can use a loop to create an associative array with all the counts:

$counts = array();
while ($row = mysqli_fetch_assoc($query) {
    $counts[$row['suffix']] = $row['count'];
}
var_dump($counts);