[PHP] 2 different tables

[wayneglossop]

[syntax="php"]<?php
mysql_pconnect("db","db","pw");
mysql_select_db("db");

$result=mysql_query("SELECT TeamName FROM telford_teams WHERE Division='1'");
mysql_query("INSERT INTO telford_1 (match_no, div, hometeam, sets_home, awayteam, sets_away, posted_on, posted_by) values ('', '1', '$result', '0', '', '0', '', '')");
?>[/syntax]

[Michael]

Hi wayneglossop,

first of all: to our forum.

And now: Could you (please) add some text to your posting? What is your problem? Please consider: We are no PHP professionals but trying to help you...

a lot!

[wayneglossop]

Whoops! Sorry'bout that. Here's what I meant to put:

Hi.

I wondered if anyone can help with this: I want to select from one database table and insert the result into another. There will be multiple rows, so I don't know if "mysql_query" is the right choice. Something like this (except it doesn't work!):

[syntax="php"]<?php
mysql_pconnect("db","db","pw");
mysql_select_db("db");

$result=mysql_query("SELECT TeamName FROM telford_teams WHERE Division='1'");
mysql_query("INSERT INTO telford_1 (match_no, div, hometeam, sets_home, awayteam, sets_away, posted_on, posted_by) values ('', '1', '$result', '0', '', '0', '', '')");
?>[/syntax]

[Michael]

Hi, again!

Your idea is not that wrong but you have to consider that the result set you arte retrieving with

[syntax="sql"]SELECT TeamName FROM telford_teams WHERE Division='1'[/syntax]

contains multiple rows (names of the teams). So if you want to put all the teams into a new table, you have to iterate (for or while-loop) over the result set and fetch each record of the result set and make an insert with this value (here: team name) like that (attention: meta-code! This example won't work!):

[syntax="sql"]
get first resulset entry
WHILE ($resultset has entries) do
get $team_name from $resulset
INSERT INTO telford_1 (match_no, div, hometeam, sets_home,
awayteam, sets_away, posted_on, posted_by) values ('', '1',
'$team_name', '0', '', '0', '', '')
get next entry from $resultset
end
[/syntax]

Regards!

[wayneglossop]

Ok. Well, erm thanks Michael, but my frustration factor has set in!
I've tried loads of ways to make it work, but failed (again).
This is the most sensible way I can think of writing the code:

[syntax="php"]<?php
mysql_pconnect("db","ddbname","password");
mysql_select_db("table");

$sql = "SELECT TeamName FROM telford_teams WHERE Division='1'";
WHILE ($resultset>0){
do_get($team_name FROM $resultset);
INSERT INTO telford_1 (match_no, div, hometeam, sets_home,
awayteam, sets_away, posted_on, posted_by) values ('', '1',
'$team_name', '0', '', '0', '', '')
next($resultset);
}
?>[/syntax]

Can you tell me where I'm going wrong?
I'm not very good at this am I!?

Cheers,
Wayne

[Michael]

Hi Wayne,

found this iteration-sample on a german php-site:

[syntax="php"]<?php
error_reporting(E_ALL);

@mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASS) OR die(mysql_error());
mysql_select_db(MYSQL_DATABASE) OR die(mysql_error());

$sql = "SELECT
Title,
xDate
FROM
News
ORDER BY
xDate DESC;";

$result = mysql_query($sql) OR die(mysql_error());

echo "News in Database:<br />\n";
if(mysql_num_rows($result)) {

// --> this is the interesting part, here (I think )
//
while($row = mysql_fetch_assoc($result)) {
echo $row['xDate']." > ".$row['Title']."<br />\n";
}
//
// <--

} else {
echo "There are now news in your database.<br />\n";
}
?>[/syntax]

So give it a try!

Regards!

P.S.: Please consider that I don't know PHP at all

[wayneglossop]

Well Michael, I really don't know.
The code you supplied works ok, but its when I try to add "INSERT INTO" the other db table, it all goes belly-up.

The search continues...

[ThomAce]

Whoops! Sorry'bout that. Here's what I meant to put:

Hi.

I wondered if anyone can help with this: I want to select from one database table and insert the result into another. There will be multiple rows, so I don't know if "mysql_query" is the right choice. Something like this (except it doesn't work!):

[syntax="php"]<?php
mysql_pconnect("db","db","pw");
mysql_select_db("db");

$result=mysql_query("SELECT TeamName FROM telford_teams WHERE Division='1'");
mysql_query("INSERT INTO telford_1 (match_no, div, hometeam, sets_home, awayteam, sets_away, posted_on, posted_by) values ('', '1', '$result', '0', '', '0', '', '')");
?>[/syntax]

Your MySQL thing isn't right.

The mysql_query just return a pointer of a mysql resource.
If you need the data from the SQL table then you need to use mysql_fetch_row, mysql_fetch_assoc, mysql_fetch_array, mysql_fetch_object, etc... (or what do you want)

So... Here is a little sample for you:

[php]

<?php

mysql_connect("db","db","pw"); //don't use persistent connection!
mysql_select_db("db");

$query = mysql_query("SELECT TeamName FROM telford_teams WHERE Division='1'");

if more than one row:
while($row = mysql_fetch_row($query)){
$result = $row[0]; //i don't know your table things...
}

else:
$result = mysql_fetch_row($query); //this is an array. you can read $result's data with array handlers

[/php]

Notice:

Never use mysql_pconnect if you don't know what you do! The normal mysql_connection is closed self when the php script is ending or the time is out (set_time_limit thing) The persistent connection is not closed when the php script is ended. That is allways opened when you open it once time. Just closed when the MySQL closing it.


Sorry for my poor english.

[Jay]

I don't know PHP but you can accomplish this with only one DML statement

INSERT INTO telford_1 (match_no, div, hometeam, sets_home, awayteam, sets_away, posted_on, posted_by)
SELECT '' match_no, '1' div, TeamName hometown, '0' sets_home, '' awayteam, '0' sets_away, '' posted_on, '' posted_by)

Regards
Jay