CODEWITHSUNDEEP
phpmysql
Devon
Devon

Reputation: 159

Get id from table 1 to match with table 2

i am new to php, i have a idea of what i want to do but i am not sure how i can execute them

i have 2 table in mysql table 1 contains:

ID|Name|AboutMe|Specialisation
Example:
101|Andrew|handsome|drawing
102|Jane|pretty|drawing
103|Daniel|cute|swimming

table 2 contains
ID|Link|Title
101|//image1.jpg|Drawing in the dark
102|//image2.jpg|Drawing in me
103|//image3.jpg|Fish swimming

I want to display some images that are link to a Specialisation only once my website run.

A flow of what i have 1)

// get all the ID with drawing as specialisation // will return 2 rows
select ID FROM table1 WHERE Specialisation = "Drawing";
id = ID

2) from each row that matches, take the ID to get the link from table 2 like so

// get row of the links from the userid
Select * FROM userdatafiles WHERE ID = id
store the links into a variable and send to my jquery to display.

So basically get the id of each row that matches drawing, and take the id to find the same id from table 2 and get the link, and send it out to jquery as a result.

As there are many rows that i have to send out and i need to use arrays & loops, i'm not sure how i can do that. Can someone guide me along to what i'm doing? Thanks!

UPDATE:

jquery:

$.ajax({
    type: "POST",
    //dataType: "json",
    url: "CMS/PHP/displayFeatThumbs.php",
    success: function(data) {
            alert(data[0]);
    }
});

PHP:

<?php
include 'dbAuthen.php';
$sql = 'SELECT * FROM userdatafiles JOIN users ON userdatafiles.UserID = users.UserID WHERE Specialisation = "Interactive Art & Technology"';

$result = mysqli_query($con,$sql);


if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo $links[] = $row["Link"];           
    }
} else {
    echo "0 results";
}
?>

no idea how to display the array index on jquery, tried both alert(data[0]), alert(data.links[0]);

Upvotes: 0

Views: 601

Answers (1)

Weexe
Weexe

Reputation: 150

You can use a natural join to connect 2 tables or more. The query should look like this :

SELECT * FROM userdatafiles JOIN table1 ON userdatafiles.id = table1.id WHERE Specialisation = "Drawing"

The result will be a new table like this example:

table1: id | name | date
table2: id | city | country

result : id | name | date | city | country

You can work with the data like this:

$result = mysql_query("Your Query");  
while($row = mysql_fetch_object($result)){  
            //Access values with $row->ColumnName  
}

Upvotes: 2

Related Questions