Passing array into mySql from a function

PHP
#1

Hi There

As part of my assignment, I am working on to fetch records from one table and then pass resulted array into another MySql query to fetch data from another table. The records to fetch are multiple against multiple.

Below is the code for fetching first record:

$sql= "SELECT * FROM subjects";
$statement= $db->prepare($sql);
$statement->execute();
$results= $statement->fetchAll();
$statement->closeCursor();

Then I use below loop to fetch ids from subjects which is a foreign key in pages table
foreach($sub as $sub )
{
$id=$sub[‘id’];
echo ($id). “
”;
}

This gives me an array which I pass to another table using below

$array=join("’,’",$id);

$sql = “SELECT * FROM pages WHERE subject_id IN (’”.$id."’) ";
$statement= $db->prepare($sql);
$statement->execute();
$result= $statement->fetchAll();
$statement->closeCursor();

foreach($result as $row) {

echo "<tr>" . $row['menu_name'] . "</tr>";

}

Unfortunately this only gives an empty array or sometime string to array error.

Any idea or help will be much appreciated.
Regards
Sam

#2

There are so many problems in your code which makes it hard to explain them all. Normally I would rewrite the whole code but I am not sure if this would help you much.

First: never user select *. Always select only the columns you really need. In this case it should be

Select Id from subjects

Next: you do not need to use two queries. You can fetch all pages and ids with one query

Select subject.id, pages.menu_name 
from subjects left join pages on pages.subject_id = subjects.id 
where pages.subject_id is not null

If you only want pages of special subjects just add it to the where like

Where subjects.id IN (1,2,3…)

Id should always be an integer (or other numeric type). So you don’t need to wrap them by ’.
Also you cannot start with a comma like you did when creating your in string.
The string you parse to your In will look like ‘’,’,1,2,3’ which is completely wrong.

2 Likes
#3

You are of course absolutely correct, but if this is an assignment for a school or college project, the OP may not yet have been taught about more advanced queries, and perhaps is being asked to demonstrate what they’ve learned so far.

Taking a few things from the original post:

I don’t see how the code you posted:

foreach($sub as $sub )
{
$id=$sub[‘id’];
echo ($id). “
”;
}

results in

It creates a normal variable called $id, not an array of that name, and it’ll only contain the last value of $sub - and on that, where does $sub come from in the first place? If you took the advice above and only retrieved the id column, you could just lose the foreach completely and implode($results) and that would be it.

Then you have this code:

$array=join("’,’",$id);

$sql = “SELECT * FROM pages WHERE subject_id IN (’”.$id."’) ";

where you convert your $id “array” (except it isn’t an array, so I’m not sure how well that will work) into a string separated by commas using a synonym of the implode() function. But then in your query, you don’t use it, you use the original $id variable. And as @Thallius said above, you’re sticking quotes around each entry - even if it was going to work as you want it to, your id column is surely a numeric column anyway, so there are no quotes required.

You’ll find that the error messages you are getting will also include the line number in your code, so that should point you towards exactly where the errors are occurring. You can also use var_dump() to examine the values of variables at various points in your code to see whether they contain what you want them to contain. If neither of these posts help you, perhaps you could be a bit more precise with the error messages and show the lines of code where they fail.

1 Like
#4

It will throw a E_WARN level event and return an empty string, as join is programmed to detect its input argument types.

Thallius is correct in that you can do this in a single query; however the given query is not the correct result based on your statements.
If you wanted all of the values in subjects, regardless of if there is a page aligned to it, you would left join without a where clause.
Saying 'WHERE page.subject_id is not null" is making the left join an inner join just with more words.

1 Like
#5

Remember that E_WARN ( every error level in fact ) is easily diggable, so its no problem to dig deeper ( down to root cause ).

@arsohail08 are there any entries in log-file/log-device)?

#6

Thanks you for your comprehensive reply

#7

Thanks for giving valuable suggestions

#8

The result I get is id numbers which starts from 1 to 8, I wanted to pass these numbers in sql query to fetch records related to these ids. How is it possible?

#9

I am trying to achieve a dynamic navigation bar which works like user will click on a subject and after clicking subject it will show list pages in the navigation bar. Lets suppose I got many subject IDs which I have to pass in one sql statement in pages table. which method I can use. I hope my statement is understandable/.