在mySQL中搜索数据和交叉引用

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 |Name1|Name2|Name3these 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 |Name|Weeknum|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 Name|Weeknum then it would list Completed, thanks in advance

I prefer if you could add a field called id and make it primary for the first table and create another field in the second table 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 :

Weeknum
Week-03
complete

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");
$FetchInfo->execute();

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

Now if you don't want to use what i suggest you can easily do this :

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

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

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 . What is that and how to fix that?

Whats is it :

SQL injection is a code injection technique, used to attack data-driven applications, in which nefarious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).

How to fix it in the example in this answer is by doing this :

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

Read this too : Prepared statements and stored procedures

Hope this help you .

What I gather from the comments is that you mean something along the lines off

Update Table2
Set Weeknum='Completed'
Where Name='Mark Abc'

I hope this is what you mean