1

I’ve trying to write a search for a mysql table, but struggling if it’s possible.

I have a table that may have multiple numbers, i.e. an object may be available in sizes [3, 6, 13, 26] Initially separating them in to different rows allowed a search, but the same event would appear for everything within the Greater/Less Than or Between search range via: -

SELECT * FROM mytable WHERE SeqNumber >=$POSTmin AND SeqNumber<=$POSTmax;

SELECT * FROM mytable WHERE SeqNumber BETWEEN $POSTmin AND $POSTmax;

Unfortunately combining them in to 1 row, but with multiple sizes [3, 6, 13, 26] would only work on the first value within the range. Searching on a single number does work with a LIKE: -

SELECT * FROM mytable WHERE SeqNumber LIKE '%$POSTvarible%';

But I would like to know if it’s possible, if so how, I can search Max & Min if that number appears at random positions in a cell?

SecretAgentMan
  • 2,856
  • 7
  • 21
  • 41
djwood150
  • 11
  • 1
  • 2
    https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – sticky bit Jan 21 '20 at 21:25
  • 2
    Can you possibly reword some of this? The way you formulated some of the sub-questions or statements is very confusing. Like, what does this mean? "Unfortunately combining them in to 1 row, but with multiple sizes [3, 6, 13, 26] would only work on the first value within the range." – Jedi_Maseter_Sam Jan 21 '20 at 22:00
  • Thanks Sticky Bit I'd like to hide behind proof of concept, but I know how to do it correctly and it's a lot of work, so it's just laziness. – djwood150 Jan 22 '20 at 15:05

1 Answers1

0

I mean you can retrieve all matched sizes in one row using GROUP_CONCAT:

<?php
$POSTmin = 6;
$POSTmax = 30;

$query = "SELECT Name, GROUP_CONCAT(SeqNumber) SeqNumbers 
FROM mytable 
WHERE SeqNumber BETWEEN ? AND ?
GROUP BY Name;";

// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute([$POSTmin, $POSTmax]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($rows);

PHP PDO GROUP_CONCAT test online

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39