INOH INOH - 1 year ago 45
MySQL Question

Searching mySQL for Data and Cross referencing

So Iam not sure how to explain what I am looking for but will explain the best I can.

I have a mySQL database with the following Column Names

these are the Column names. Under each Column there is a name
|Mark Abc|Jim Abc|Kim Abc
. Ok that is part 1, next I have another Table with the following Columns
so when i update this table it would have some data like
|Mark Abc|Week-03|

So the question I have is how do i create a PHP script that will take the value from under Name1 and search for that name under name and weeknum and return a value like this-
Week-01 Week-02 Completed Week-04

So basically will list all the 52 Weeks and if any are found to match
then it would list
, thanks in advance

Answer Source

I prefer if you could add a field called id and make it primary for the first table and create another field with the same name ( ID ) and it will be foreign key then is easy to do what you were asking for , cause every name has its own id .

    Table 1

    ID    NAME  
    1     Mark Abc
    2     Jim Abc
    3     Kim Abc

    Table 2

    ID  Weeknum
    1   Week-03
    1   complete
    3   Week-09

Now lets assume that i wanted to execute this query :

" SELECT Weeknum FROM TABLE 2 WHERE id = 1; "

This will give us this output :


What happened is Mark Abc has the id = 1 and we searched for his id Table 2 .

If you did what i suggest this Query will do the job

i will assume that you are already linked your database and i will just give you the query : i will use PDO to type the query :

$FetchInfo = $conn>prepare("SELECT Weeknum FROM TABLE 2 WHERE id = 1");

While($FI = $FetchInfo->FETCH(PDO::FETCH_ASSOC))
   echo $FI['weeknum']."\n";

Now if you dont want to use what i suggest you can easily do this :

    $FetchInfo = $conn>prepare("SELECT Weeknum FROM TABLE 2 WHERE NAME = Mark Abc ");

    While($FI = $FetchInfo->FETCH(PDO::FETCH_ASSOC))
       echo $FI['weeknum']."\n";

Tow Notes :

  • I used PDO to type and execute the query , if you don't know what is it let me know so i can edit the answer and replace my PDO code to Mysqli code .
  • Just for the future when you want to type query don't do this:

$FetchInfo = $conn>prepare("SELECT Weeknum FROM TABLE 2 WHERE id = 1"); $FetchInfo->execute();

Its so danger , and it could be opened for SQL INJECTION . to fix that you can do this :

$FetchInfo = $conn>prepare("SELECT Weeknum FROM TABLE 2 WHERE id = ?");

I will add more links when i get back home .

Hope this help you .