Question

help with mysql+php.
i need to fill up a table with information drawn from other different tables. how can this be done? which functions?

Answer 1

Using SELECT with INSERT
http://www.w3schools.com/sql/sql_insert_into_select.asp

Answer 2

but if I were doing it in php, then..?

Answer 3

ok, in PHP you have functions to deal work with mysql:
http://php.net/manual/en/mysql.examples-basic.php
You use `mysql_query("sql_query_here")` along with an SQL query to execute it on the database.
So from that point you just have to form an SQL query and pass it to the function.
Are you familiar with SQL?

Answer 4

somewhat. i'm learning as i go along with this assignment. this is the last part.

Answer 5

my classmate suggested to use join and get functions. what do you think?

Answer 6

I'm not sure what you mean by 'get' function, is that SELECT?
I'd use SELECT with JOIN to get all the values I'm interested in returned from the tables and then use it along with INSERT as shown here in order to insert them to the new table.

The first step is to use SELECT alone and see that you can get a list of all the interesting values
http://www.w3schools.com/sql/sql_join.asp
The SELECT will return a list of values as you specified which usually come directly from values of retrieved row.

Once you have all the values retrieved they can be passed directly to INSERT which will treat them as lists of values to for new rows. Each list retrieved by the SELECT will be used for the values of a new row.

I can't get much more specific without more details, but I hope it helps

Answer 7

he told me its used to get a value and save it on the url. but thank you for everything. i'll try to draw a diagram for better interpretation.

Answer 8

Maybe I'm missing something, Why do you need to pass anything to another page?
Isn't it database operation only? I thought you just wanted to take data from several tables and use it to add new rows to your new table

Answer 9

I'd like to add that php mysql functions are the classical old-fashioned way to work with mysql, but there is a newer object oriented alternative - mysqli:
http://php.net/manual/en/mysqli.quickstart.statements.php
I personally like it better =)

Answer 10

oh i forgot to mention. this table i'm creating, its displayed on another page when you click on a link. so on the first page, there are two tables. All the rows in the first column of the 2nd table are texts(turned to links) and when you click on one, a table is displayed on another page. each table is different depending on the link you click on. and this is the table i am generating.

Answer 11

oh yea, i wanted to use sqli but many examples/samples similar to my homework i found on the internet for help were using the old fashion sql. So, I used sql for now lol

Answer 12

Hold on, the term 'table' is problematic here. It could refer to an actual table in the database or a way to display the data in the webpage.
When you say you want to take data from two tables I assume they are actual tables in the data base.
So when you say you want to 'fill up' a table with that information, does it mean you want to create an actual table in the database with the information or just display a table in your website with the information?

Answer 13

the latter. just displaying the table on another page.

Answer 14

yea, the other tables are in database

Answer 15

I see, then in that case you don't need any INSERT. you just need to get the data you're interested in using SELECT and JOIN
If you have different display options then it does make sense to have a GET parameter telling the webpage which way to show.
Are you familiar with SELECT? JOIN? HTML tables?

Answer 16

select and html tables. i'm reading on join now

Answer 17

Ok, after executing the SELECT query in PHP you can get back the returned lists, one by one, using `mysql_fetch_array()`:
http://php.net/manual/en/function.mysql-fetch-array.php
Look at example #2 there, it's nice. Instead of printing it the way they did, you can print a row of an HTML table instead, for example.

with mysqli it's done a little differently:
http://php.net/manual/en/mysqli-result.fetch-array.php
Look at example #1

Answer 18

so i've attached a drawing of what i need to do. basically, there are tables made which shows every SID is matched with a few OID's in a table. every OID is matched with a few GID's in another table. 3rd table has an OID with its own OID statements. Every SID is a link. Lastly, the table at the bottom is what should be displayed in a new page when clicked in a SID link, which is its OID's on a separate row, then the next column has the OID's statement for each OID, then the third column has all the GID's in the same cell for each OID.

Answer 19

o_O mysqli does look a little cleaner than the standard one.

Answer 20

What is the top left table?

Answer 21

The top table consists of a list of SID's in the first column and its corresponding SID statements in the 2nd column. 2nd column doesn't matter in this case.

Answer 22

I have a question. When I make the tables, do i need to set the columns to PRIMARY for index in myphpadmin? I heard it helps in linking between columns or between different tables.

Answer 23

Read those:
http://itknowledgeexchange.techtarget.com/sql-server/difference-between-an-index-and-a-primary-key/
http://stackoverflow.com/questions/3844899/difference-between-key-primary-key-unique-key-and-index-in-mysql
http://dev.mysql.com/doc/refman/5.5/en/optimizing-primary-keys.html

In short, index will help you search quicker and PRIMARY KEY assures unique value for identifying the row among one or multiple columns, which allows optimizations for even quicker search on it.

Answer 24

oo ok. but initially, i didn't assign the columns any index when making the tables. is it ok if I don't use any? can i still make the connections between the tables without it?

Answer 25

So let me see if I get it. You have SID associated with OIDs which each one is associated with GIDs in turn?
And now you want to show all the OIDs associated with the clicked SID along with their GIDs?

Answer 26

Yes you don't have to use any index or primary key, those are used for optimizations.
You can work without them as well and maybe add later

Answer 27

oops, sorry didn't see the comment. yes, that's correct. and along with the GID's, show the their OID statements as well. Right now, I've managed to display the table. Got the first column working. Now, I'm trying to get the other two columns.

Answer 28

Ok, you didn't tell me the names of the tables so I made 3 of them like this:
Table SID_OID with integer columns SID and OID
Table OID_STMT with integer column OID and text column STMT
Table OID_GID with integer columns OID and GID.

Now I made this query:
```sql
SELECT
`OID_STMT`.`OID`,
`OID_STMT`.`STMT`,
# Comma separated list of all the GIDs associated with the OID
GROUP_CONCAT(`GID`) AS "GIDs"
FROM `OID_GID`
# Join with OID_STMT in order to have the OID statement for every row
INNER JOIN `OID_STMT` ON `OID_STMT`.`OID` = `OID_GID`.`OID`
# Pick only rows with OID associated with a specific SID (in this case where SID=1)
WHERE `OID_STMT`.`OID` IN (SELECT `OID` FROM `SID_OID` WHERE `SID` = 1)

# This will group rows with same OID in the table OID_GID together.
# The SELECT will then go over those grouped rows, which means every generated
# row will be for a different OID.
# The GROUP_CONCAT above will take all the GID values of the grouped
# rows (rows with same OID) and list them as comma separated list.
GROUP BY `OID_STMT`.`OID`
```

My tables looked like this:

Table SID_OID:
SID | OID
________
1 | 1
1 | 3
2 | 2

Table OID_STMT:
OID | STMT
__________
1 | OID #1
2 | OID #2
3 | OID #3

Table OID_GID:
OID | GID
________
1 | 11
1 | 12
1 | 13
2 | 21
2 | 22
2 | 23
3 | 31
3 | 32
3 | 33

The query returned this:
OID | STMT | GIDs
____________________
1 | OID #1 | 11,12,13
3 | OID #3 | 31,32,33

It means that now in PHP the fetched array for each row will have the keys 'OID', 'STMT' and 'GIDs' with the corresponding values for that row.
Before executing the query you can generate it with different values for SID instead of `SID = 1`

Answer 29

oO i tried doing it without the join. This is for the first column to get the OIDs. It works
$sid = $_GET["sid"];
$sql = "SELECT OID FROM Matching_SID_OID WHERE SID = $sid";
$result = $mysqli->query($sql);

while ($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>";
echo $row["OID"];
echo "</td>";
echo "</tr>";

}

Answer 30

Ye, I used something like that as a sub-query in mine. Compare and see what I mean =)

Answer 31

thanks. yea, but now, im having trouble displaying the second column. like in the case for the one above that has "WHERE SID = $sid", i had WHERE OID = " . $row['oid'] to display the OID STMT. Its not working though.
$sql = "SELECT OBJECTIVE_STATEMENT FROM List_Of_Objectives WHERE OID = " . $row['oid']; //this is the full line

Answer 32

The better way to do it is using JOIN, but you can do it without it.
Would you mind posting the code?

Answer 33

no need. i just finished my assignment. thank you for everything :)

Answer 34

Sure =)