Select an array from one databank and compare it to one from another databank

Question

I know the title is not the best, but it somehow summarizes the problem: I run a page on which clients can place a request for a mentor in a specific subject. Now I have a databank for my mentors with their particular subjects and a databank for the incoming requests from the clients. What I want to do now is, to write an SQL Command that compares the subject of the incoming request to the subjects of my mentors, so that the teachers, who teach this subject, will see the request when they log in. So as an example: Mother Anita asks for a teacher in maths -> sends the request. The code checks which teachers teach maths und display the request to those teachers when they log in. Please keep in mind that I am a bloody beginner.

The code I currently is the following:

// Connect to the teacher databank and select the subjects of the teacher that has logged in

$connect = mysql_connect('xxx', 'xxx', 'xxx');
mysql_set_charset("utf8", $connect);
mysql_select_db('xxx');

$sql = "SELECT subjects FROM teachers WHERE teacher LIKE '%". $_SESSION['user'] ."%'" ;
$erg = mysql_query(@$sql);

if (false === $erg) {
die (mysql_error());
}

$speicher = mysql_fetch_array($erg, MYSQL_ASSOC);
print_r ($speicher);  

// Until here it functions and the array ($speicher) contains the subjects of the teacher

// Now connect to the other Databank (with the requests)

$connect = mysql_connect('xxx', 'xxx', 'xxx');
mysql_set_charset("utf8", $connect);
mysql_select_db('xxx');

$sql = "SELECT street, class, subject, school FROM requests WHERE subject IN ('%s')";

$inElems = array_map(function($elm) {
return "'" . mysql_real_escape_string($elm) . "'";
}, $speicher);

$sql1 = sprintf($sql, join(",", $inElems));

echo $sql1;

// It correctly spills out the SELECT Query but it is wrong because of the '' around the subjects are missing -> see output

$erg = mysql_query($sql1);

if (false === $erg) {
die (mysql_error());
}

$content = mysql_fetch_array($erg, MYSQL_ASSOC);
print_r ($content);

And the output:

SELECT street, class, subject, school FROM requests WHERE subject IN ("English, German, Mathematics")

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'English, German, Mathematics")' at line 1

So I don't know how to set it up that it is correct, currently it does not work because of the missing '' between the subjects, but I don't know any other way. I am quite much a beginner. And I know, I really need to change it to MySQLi and I will do it as soon as this problem is solved.

ehm what exactly do you mean? how can I summarize the table structure? the request table includes the necessary varchars with the contact information and the subjects, and the teacher table includes the varchars with the necessary teacher information including their subjects — Franky2207, Nov 24 '15 at 19:32
You **really** should not be writing code that relies on `mysql_` functions anymore. The MySQL extension has been deprecated for years (ever notice these red warning boxes in the documentation?) and is about to be dropped in the upcoming PHP7 release, which can be released any time now. Also see [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). On an up-to-date server, this code has a life span of about a month, after which it will **stop working**. — Oldskool, Nov 24 '15 at 19:37

Emanuel Gianico · Answer 1 · 2015-11-24T19:52:33.677

0

You can't use only join to do that. First, you have to add the aphostrophes to the items

$inElems = array_map(function($elm) { 
    return "'" . mysql_real_escape_string($elm) . "'";
}, $speicher);

The mysql_real_escape_string is to prevent SQL Injection. It's not the recommended way, but let's keep this simple.

And then, use array join

$sql1 = sprintf($sql, join(",", $inElems));

As mentioned by Oldskool mysql_* functions will be deprecated soon or later, I suggest you to use PDO instead

Let me know if it works :)

Cheers

edited Nov 24 '15 at 19:52

answered Nov 24 '15 at 19:45

Emanuel Gianico

330
6
19

Dear Emanual, thanks for your effort, appreciate it! Sadly, it does not work. I will edit in my current code (with your code proposal) and the corresponding output in my initial post above. – Franky2207 Nov 24 '15 at 21:13
Franky, could you tell me why isn't working? In your code check the SQL line with the **IN ('%s')** and remove the '' around the %s like this **IN (%s)** – Emanuel Gianico Nov 25 '15 at 16:18
Tried that, received this: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s)' at line 1 - but I am currently rewriting my tables and my forms and my site so that it harmonizes with normalization, lots of work, but I guess thats the only thing that makes sense (and will finally make the request possible). Still, I really appreciate your effort, thank you Emanuel! – Franky2207 Nov 26 '15 at 11:02
+Franky seems like the %s isn't replaced. You could post the $sql1 output? – Emanuel Gianico Nov 26 '15 at 18:55
Hey Emanuel, sorry, don't have the output anymore. I have already changed the tables, as I am currently normalizing them, so I would have to change everything back to post the former output. – Franky2207 Nov 26 '15 at 20:54

Select an array from one databank and compare it to one from another databank

1 Answers1