1

To start off, I've read the handful of questions here asking similar questions but nothing that I could take example of and implement properly into my code. I've got an array that hold a few thousand numbers, they're the names of a file in a directory. I need to run a MySQL Query that searches for those file names, but I need all the individual queries to be in another array.

Here's my code to grab the filenames:

$dir = "C:/Users/EyeCandy-Brian/Desktop/jobs";

$files = array_diff(scandir($dir), array('..', '.'));

function truncate(&$fcn) {
     $fcn = substr($fcn, 0, 6); // The original is '100100.dat', I only need the number
};

array_walk($files, "truncate");

The array looks like this:

2 => string '100100'
3 => string '100101'
4 => string '100102'
5 => string '100103'
// About 1,500+ Total

And here's my foreach loop with my declared variables:

$limit = 0; // This is for testing, stops after 5 iterations.
$qryArrNum = 2; // The index for the $files array, [0] & [1] were '.' & '..'
$count = 0; // Which index I want to store the string in, starting at [0]

$qry = array(); // I declare $qry as an array

foreach ($files as &$qry) {
    $qry[$count++] = "SELECT * FROM `table` WHERE `sku` LIKE '%$files[$qryArrNum]%'";
    ++$qryArrNum;
    if (++$limit == 5) {
        break;
    };
};

Here's where it gets wild. When using var_dump($qry); I get '1001S4' which shouldn't be. Running var_dump($files); The first five strings of the array are:

2 => string 'S00100'
3 => string '1S0101'
4 => string '10S102'
5 => string '100S03'
6 => string '1001S4'

Then following that are the expected strings from the filenames:

7 => string '100105'
8 => string '100106'
9 => string '100107'
// On for 1,500+ again

So my question is, how do I get $qry to be an array that contains a string, each string being the MySQL Query containing a search for the first value from $files, and the next string in the $qry array will search for the next value from $files. Also, why are the first handful values in $files completely different than what's expected?

NOTE: I understand that running a thousand plus MySQL Queries sequentially will murder my server, I'll use implode(); to combine them into one large query, I just need to figure this out initially.

7
  • 1
    You have a variable outside ($qry) that's the same in the foreach value. Why? Commented Dec 21, 2017 at 22:14
  • 2
    foreach ($files as &$qry) is what is messing you up. change it to foreach ($files as $file) then you can use $qry[$file] = "SELECT * FROM... Commented Dec 21, 2017 at 22:15
  • 3
    You seem to be overcomplicating things here. Commented Dec 21, 2017 at 22:16
  • 1
    Also $qry[$count++] = is kinda pointless if you're going 0,1,2,3,4 etc, just use $qry[] = and the index will automatically increment numerically. Commented Dec 21, 2017 at 22:17
  • @Scuzzy Oh my goodness, I swear I read over that a million times and it looked fine to me, I need more coffee. Thank you a ton. And I tried $qry[] initially but it threw an error, which I guessing is due to the $files as $qry part being incorrect. Commented Dec 21, 2017 at 22:17

1 Answer 1

1

Instead of running multiple queries you could use the mysql find_in_set(). https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set Once you have your files, use implode to convert to a string

$tmp = implode(",",$files);

Your sql statement would be:

$sql = "SELECT * FROM `table` WHERE FIND_IN_SET(`sku`,'{$tmp}')>0";

This will return all the matching items

Sign up to request clarification or add additional context in comments.

2 Comments

It also looks like he wants to break out of the loop after 5 queries if I am reading OP code correctly. If so, maybe add LIMIT 5 to the SQL query?
@CMiller the variable $limit is there strictly for testing purposes, in WAMP I have it stop at 5 so I don't have to wait forever for the loop to iterate thousands of times. It will be removed when I implement the imploding of the queries and push it live, thank you though!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.