[SOLVED] php file to work 2 databases help

shambala · November 27, 2007

I have a form that asks for a number, when the user types in the number it enters that number into a database. I have a different database that has every number that a user will enter inside it along with names that correspond to that number. I would like for the php file that enters the number in the database to look for the name that goes with it and enter it in.

MadTechie · November 27, 2007

What do you have so far ?

post your code

SELECT name FROM table WHERE number = $num

shambala · November 27, 2007

The form

<html>
<body>

<form action="submit.php" method="post">
Student Number: 
<input type="text" name="number" maxlength="7" size="7" />
<br>
Book's Title: 
<input type="text" name="title" />
<br>
Author of the book: 
<input type="text" name="author" />
<br>
Number of pages in the book: 
<input type="text" name="pages" maxlength="5" size="5" />
<br>
Would you recommend this book to a friend?: 
<input type="radio" name="recommend" value="yes" /> Yes
<input type="radio" name="recommend" value="no" /> No
<br>
Why? 
<br>
<textarea rows="10" cols="30" wrap="virtual" name="why" /></textarea>
<br>
<input type="submit" />
</form>
</body>
</html>

PHP file

<?php
$con = mysql_connect("localhost","root","******");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("student_data", $con);

$sql="INSERT INTO student_data (number, name, date, teacher, title, author, pages, recommend, why)
VALUES
('$_POST[number]','$_POST[name]',CURDATE(),'$_POST[teacher]','$_POST[title]','$_POST[author]','$_POST[pages]','$_POST[recommend]','$_POST[why]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Thank you for participating in Durant's Cougar's Excellence in Reading Program<br><br>Click here to <a href=http://*****>Submit another reading</a>";

mysql_close($con)
?>

I would like for it to grab the teacher and name data from the other database according to the numer then input that all in the student_data table. Right now everything works except the teacher and name part.

shambala · November 28, 2007

*bump*

shambala · November 28, 2007

I tried this bit of coding but this doesnt work, so any help to fix it would be great

<?php
$con = mysql_connect("localhost","root","*****");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("student_data", $con);

$result=mysql_query("SELECT $_POST[number] FROM all_students");
$first_name = mysql_result($result,0,"first_name")
$last_name = mysql_result($result,0,"last_name")
$teacher = mysql_result($result,0,"teacher")
$room = mysql_result($result,0,"room")


$sql="INSERT INTO student_data (number, first_name, last_name, date, teacher, title, author, pages, recommend, why, room)
VALUES
('$_POST[number]',$first_name,$last_name,CURDATE(),$teacher,'$_POST[title]','$_POST[author]','$_POST[pages]','$_POST[recommend]','$_POST[why]',$room)";

if (!mysql_query($sql,$con))
{
  die('Error: ' . mysql_error());
}
echo "Thank you for participating in Durant's Cougar's Excellence in Reading Program<br><br>Click here to <a href=http://****>Submit another reading</a>";

mysql_close($con)
?>

MadTechie · November 28, 2007

your probably want something like this

$result=mysql_query("SELECT * FROM all_students WHERE number = {$_POST['number']} ");

but your need to echo the results, i'm not sure that will work as your inseting into student_data table but selecting from the all_students table!, so i am even more confused about what your attempting!

shambala · November 28, 2007

It is a bit confusing =) but basically what I want is this:

When the user types their number on the form and hits submit i want the php file to grab the teacher and name data that correspond to the number from the all_students table and input that data into the student_data table.

MadTechie · November 28, 2007

i want the php file to grab the teacher and name data that correspond to the number from the all_students table and input that data into the student_data table.

and without knowing that data myself i have no way of helping...!

do you have a database schema and some sample data ?

shambala · November 28, 2007

Here is a graphical layout of what im trying to say along with table structures and test data =D (I recreated the database/tables since im not at my server, things might not be exactly the same as my script)

http://www.fa-studios.downthe.net/phpprob.bmp

MadTechie · November 29, 2007

Looks like a simple join..

try this in PMA (phpmyadmin)

SELECT S.number, All.first_name FROM all_students as All 
LEFT JOIN student_data as S ON S.number= All.number
WHERE S.number = 1234

shambala · November 29, 2007

Error

SQL query: Documentation

SELECT S.number, All.first_name
FROM all_students AS
ALL LEFT JOIN student_data AS S ON S.number = All.number
WHERE S.number =1234
LIMIT 0 , 30

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'All  LEFT JOIN student_data as S ON S.number = All.number  WHERE S.number = 1234' at line 1

This does sound like what I need though, for data from one table to be entered into another table by a number thats in both tables.

teng84 · November 29, 2007

ALL is a reserve word

try A_LL or back tick `ALL`

shambala · November 29, 2007

[b]MySQL returned an empty result set (i.e. zero rows). (Query took 0.0001 sec)[/b]


SQL query:
SELECT S.number, A_ll.first_name
FROM all_students AS A_ll
LEFT JOIN student_data AS S ON S.number = A_ll.number
WHERE S.number =1234
LIMIT 0 , 30

But if I change 1234 to 1234567 I get

[b]Showing rows 0 - 0 (1 total, Query took 0.0001 sec)[/b]


SQL query:
SELECT S.number, A_ll.first_name
FROM all_students AS A_ll
LEFT JOIN student_data AS S ON S.number = A_ll.number
WHERE S.number =1234567
LIMIT 0 , 30

It displays number and first_name beloew that but when I go to browse student_data nothing has changed

MadTechie · November 29, 2007

but when I go to browse student_data nothing has changed

It won't change..Why would it change ???

your viewing not updating what are you expecting ?

shambala · November 29, 2007

I see what your saying now, its my fault for not explaining better. I want the data from all_students to copy over to student_data based on the number value

wsantos · November 29, 2007

shambala without a clearer description of the problems gives you lower chances of being helped. can you run this...

mysql>show columns from all_student;
mysql>show columns from student_data;

shambala · November 29, 2007

student_data


Field 	           Type      	Null 	  Key 	  Default       	Extra
number 	        int(7) 	  	  	 
first_name 	varchar(50) 	  	  	  	 
last_name      varchar(50) 	  	  	  	 
date 	date   0000-00-00 	 
teacher 	varchar(50) 	  	  	  	 
title 	           varchar(50) 	  	  	  	 
author           varchar(50) 	  	  	  	 
pages   	int(5) 	  	  	 
recommend    varchar(3) 	  	  	  	 
why 	         text 	  	  	  	 
room 	         varchar(5)

all_students


Field 	           Type 	   Null 	Key 	Default 	Extra
number 	        int(7) 	  	  	 
first_name 	varchar(50) 	  	  	  	 
last_name 	varchar(50) 	  	  	  	 
teacher 	varchar(50) 	  	  	  	 
room 	         varchar(5)

MadTechie · November 29, 2007

I think i know what you want to do..

if the "number" is prim, then why not export the data as "update type" then import into the new database ?

of course do a backup first1

shambala · November 29, 2007

I dont know how to do that =S

But that is what I want to do: Export data from all_students and import into student_data, but it need to import the data into the row that has the same "number"

wsantos · November 29, 2007

Im not really sure why in the world you would want to do that since you can utilize the number to establish the relationship between the two tables. Anyway you can try this as your query

INSERT INTO student_data (number,first_name,last_name,teacher,room) SELECT number,first_name,last_name,teacher,room FROM all_student WHERE number = $num

shambala · November 29, 2007

Thank you so much wsantos it worked perfectly but it input the data into a new row instead of merging it into the row thats already there

teng84 · November 29, 2007

try replace

REPLACE INTO student_data (number,first_name,last_name,teacher,room) SELECT number,first_name,last_name,teacher,room FROM all_student WHERE number = $num

wsantos · November 29, 2007

Now you confused me if it were my english that have some issues.

Note that replace deletes the old record before inserting the new one. Hence if you got auto increment in your number field you might encounter some issues.

You can try this. Joined Update

UPDATE student_data SET student_data.number=all_student.number,student_data.first_name=all_student.first_name,student_data.last_name=all_student.last_name,student_data.teacher=all_student.teacher,student_data.room=all_student.room WHERE all_student.number = $num AND student_data.number=all_student.number

Note... Use the power of search engines

shambala · November 29, 2007

UPDATE student_data SET student_data.number=all_students.number,student_data.first_name=all_students.first_name,student_data.last_name=all_students.last_name,student_data.teacher=all_students.teacher,student_data.room=all_students.room WHERE all_students.number = 1234567 AND student_data.number=all_students.number

It gave me a error saying:

#1109 - Unknown table 'all_students' in where clause

wsantos · November 29, 2007

Try this.

UPDATE student_data SET student_data.number=all_students.number,student_data.first_name=all_students.first_name,student_data.last_name=all_students.last_name,student_data.teacher=all_students.teacher,student_data.room=all_students.room WHERE student_data.number = 1234567 AND student_data.number=all_students.number

Sign In

[SOLVED] php file to work 2 databases help

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information