Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Web Coding › PHP/MySQL Help (Inserting Values into multiple tables.
New Posts  All Forums:Forum Nav:

PHP/MySQL Help (Inserting Values into multiple tables.

post #1 of 6
Thread Starter 
I have a web page that has a form with 5 text boxes for input. They are for a musician/band db I am working on. The artist id, first name and last name are stored in the Artist table. The Artist's id and band id are stored in a band-to-artist table, and the artist id and instrument id are stored in a artist-to-instrument table. I would like this one form to be able to insert into all of these tables. Here is what I have so far:
Code:
<?php
//open connection
$con = mysql_connect("localhost","root","root");
if (!$con){
        die('Could not connect: ' . mysql_error());
        }
        
mysql_select_db("recording_studio", $con);
//end opening connection

$art_id = $_POST['art_id'];
$art_fname = $_POST['art_fname'];
$art_lname = $_POST['art_lname'];
$art_band = $_POST['art_band'];
$art_ins = $_POST['art_ins'];

if(empty($art_id) || empty($art_fname) || empty($art_lname) || empty($art_band) || empty($art_ins)){
        echo '<script type="text/javascript"> alert("One or more fields were left empty! Please try again!");</script>';
}else{
//artist info
        $sql="INSERT INTO artist (artist_ID, artist_fname, artist_lname)
        VALUES
        ('$_POST[art_id]','$_POST[art_fname]','$_POST[art_lname]')";
        mysql_query(sql);       
        
        $sql1="INSERT INTO plays_in (artist_ID, band_ID)
        VALUES
        ('$_POST[art_id]','$_POST[art_band]')";
        mysql_query(sql1);
        
        $sql2="INSERT INTO artist_plays (artist_ID, instrument_ID)
        VALUES
        ('$_POST[art_id]','$_POST[art_ins]')";
        mysql_query(sql2);
        
                //if (!mysql_query($sql,$sql1,$sql2,$con)){
                //die('Error: ' . mysql_error());
                //}
                
                
        echo '<script type="text/javascript"> alert("1 record has been added!");</script>';
}


//close connection
mysql_close($con);
?>


I am receiving an error when I try and execute these inserts. Any help would be great, I'm rather stuck. Thanks.
600t SE Build
(13 items)
 
 
Laptop
(3 items)
 
CPUMotherboardGraphicsRAM
AMD Phenom II X6 1090T Asus Crosshair IV Formula 890FX XFX Radeon HD 6870 1GB [980/1200|1.25V] 8GB DDR3 1333 G.Skill RipJaw Series 
Hard DriveOSKeyboardPower
Mushkin 40GB SSD + 300GB WD VelociRaptor Windows 7 Razer Lycosa Corsair HX850 
CaseMouse
Corsair Graphite 600t SE Razer Deathadder 
CPUMotherboardGraphicsRAM
Athlon 64 X2 5000+ Black Edition Asus M2N-SLI Deluxe 8800GT in SLI OCZ Platinum Revision 2GB PC6400 DDR2 800 
OSPowerCase
Windows 7 Ultimate 64-bit OCZ GameXstream 700w Antec 900 
CPUGraphicsOS
Intel Core i5 NVS 3100M Windows 7 Professional 32-bit 
  hide details  
Reply
600t SE Build
(13 items)
 
 
Laptop
(3 items)
 
CPUMotherboardGraphicsRAM
AMD Phenom II X6 1090T Asus Crosshair IV Formula 890FX XFX Radeon HD 6870 1GB [980/1200|1.25V] 8GB DDR3 1333 G.Skill RipJaw Series 
Hard DriveOSKeyboardPower
Mushkin 40GB SSD + 300GB WD VelociRaptor Windows 7 Razer Lycosa Corsair HX850 
CaseMouse
Corsair Graphite 600t SE Razer Deathadder 
CPUMotherboardGraphicsRAM
Athlon 64 X2 5000+ Black Edition Asus M2N-SLI Deluxe 8800GT in SLI OCZ Platinum Revision 2GB PC6400 DDR2 800 
OSPowerCase
Windows 7 Ultimate 64-bit OCZ GameXstream 700w Antec 900 
CPUGraphicsOS
Intel Core i5 NVS 3100M Windows 7 Professional 32-bit 
  hide details  
Reply
post #2 of 6
What error are you getting?
Bandaids
(15 items)
 
  
MotherboardGraphicsHard DriveOptical Drive
Asrock Z77 Extreme 6 GTX 580 WD 10EALX ASUS DRW 
CoolingOSMonitorMonitor
Havik 140 Windows 7 Ultimate ASUS VH228T Toshiba 32RV600A 
MonitorKeyboardPowerCase
Compaq S2021a Microsoft Wired Keyboard 600 Aero Cool Strike X 1100w Asus Antec 
MouseMouse PadAudio
Logitech MX518 Mionix Ensis 320 Creative 2.1 
  hide details  
Reply
Bandaids
(15 items)
 
  
MotherboardGraphicsHard DriveOptical Drive
Asrock Z77 Extreme 6 GTX 580 WD 10EALX ASUS DRW 
CoolingOSMonitorMonitor
Havik 140 Windows 7 Ultimate ASUS VH228T Toshiba 32RV600A 
MonitorKeyboardPowerCase
Compaq S2021a Microsoft Wired Keyboard 600 Aero Cool Strike X 1100w Asus Antec 
MouseMouse PadAudio
Logitech MX518 Mionix Ensis 320 Creative 2.1 
  hide details  
Reply
post #3 of 6
Thread Starter 
I wasn't actually getting an error it was just not inserting data into the tables. Solved the problem though, i was putting mysql_query(sql) instead of mysql_query($sql).
600t SE Build
(13 items)
 
 
Laptop
(3 items)
 
CPUMotherboardGraphicsRAM
AMD Phenom II X6 1090T Asus Crosshair IV Formula 890FX XFX Radeon HD 6870 1GB [980/1200|1.25V] 8GB DDR3 1333 G.Skill RipJaw Series 
Hard DriveOSKeyboardPower
Mushkin 40GB SSD + 300GB WD VelociRaptor Windows 7 Razer Lycosa Corsair HX850 
CaseMouse
Corsair Graphite 600t SE Razer Deathadder 
CPUMotherboardGraphicsRAM
Athlon 64 X2 5000+ Black Edition Asus M2N-SLI Deluxe 8800GT in SLI OCZ Platinum Revision 2GB PC6400 DDR2 800 
OSPowerCase
Windows 7 Ultimate 64-bit OCZ GameXstream 700w Antec 900 
CPUGraphicsOS
Intel Core i5 NVS 3100M Windows 7 Professional 32-bit 
  hide details  
Reply
600t SE Build
(13 items)
 
 
Laptop
(3 items)
 
CPUMotherboardGraphicsRAM
AMD Phenom II X6 1090T Asus Crosshair IV Formula 890FX XFX Radeon HD 6870 1GB [980/1200|1.25V] 8GB DDR3 1333 G.Skill RipJaw Series 
Hard DriveOSKeyboardPower
Mushkin 40GB SSD + 300GB WD VelociRaptor Windows 7 Razer Lycosa Corsair HX850 
CaseMouse
Corsair Graphite 600t SE Razer Deathadder 
CPUMotherboardGraphicsRAM
Athlon 64 X2 5000+ Black Edition Asus M2N-SLI Deluxe 8800GT in SLI OCZ Platinum Revision 2GB PC6400 DDR2 800 
OSPowerCase
Windows 7 Ultimate 64-bit OCZ GameXstream 700w Antec 900 
CPUGraphicsOS
Intel Core i5 NVS 3100M Windows 7 Professional 32-bit 
  hide details  
Reply
post #4 of 6
Why do you do this
Code:
('$_POST[art_id]','$_POST[art_fname]','$_POST[art_lname]')";

if you're assigning the values to some variables on the previous rows?
Also, you should sanitize the inputs before inserting everything in your tables.
post #5 of 6
Please excuse the lack of code tags, I'm stuck with no editor bar at work, but try enclosing columns and tables in ``.

$sql="INSERT INTO artist (artist_ID, artist_fname, artist_lname)
VALUES
('$_POST[art_id]','$_POST[art_fname]','$_POST[art_lname]')";
mysql_query(sql);

$sql1="INSERT INTO plays_in (artist_ID, band_ID)
VALUES
('$_POST[art_id]','$_POST[art_band]')";
mysql_query(sql1);

$sql2="INSERT INTO artist_plays (artist_ID, instrument_ID)
VALUES
('$_POST[art_id]','$_POST[art_ins]')";


As d3viliz3d, you should probably do some checks in the beginning of your code to see if values are set in $_POST and save them to their own variables then make sure they are clean so you don't get and nasty MySQL injection risks.
CHILZ - Lan Rig
(17 items)
 
CANARY - Main Rig
(16 items)
 
CADILLAC - HTPC
(14 items)
 
CPUMotherboardGraphicsRAM
Intel i5-4570s Asus H97M-Plus AMD R9 280 G.Skill RipjawsX 16 GB (2x8) 
Hard DriveHard DriveHard DriveCooling
250 GB Samsung 840 240 GB Kingston 3 TB USB 3.0 Drive Custom Loop 
OSMonitorKeyboardPower
Windows 10 Preview BenQ GL2450 Filco MajesTouch2 Ninja PC P&C Silencer Mk III 600 W 
CaseMouseMouse PadAudio
Fractal Design Core 1000 Mionix Castor Monoprice XXL JL Amps + Custom Morel Bookshelf speakers 
Other
Scythe Kama-Panel 3 
CPUMotherboardGraphicsRAM
FX-8150 @ 4.6 GHz Fatal1ty 990FX Pro 9800 GTX+ 512 MB G.Skill Ripjaws X 1866 CL9 
Hard DriveHard DriveCoolingOS
120 GB OCZ Vertex 3 1 TB WD Black 5x 120mm + MCP350 + EK Supreme HF + MicroRes Windows 8 Consumer Preview 
MonitorKeyboardPowerCase
2x Dell U2212HM Logitech G110 Cooler Master 850W Silent Pro Cooler Master 690 II Adv. 
MouseMouse PadAudio
Razer Death Adder 3.5G Staples Gel Cushion Asus Xonar DG + Senn. PC333D 
CPUMotherboardGraphicsRAM
Q6600 Acer X1800 ATI 5670 2 GB Kingston 
Hard DriveOptical DriveOSMonitor
2 TB WD Green Asus BD-R Windows 7 Home Premium Sony 50" LCD 
KeyboardPowerCaseMouse
Acer Media 220 W SFF Acer X1800 Acer Optical 
Mouse PadAudio
The TV cabinet Denon 2808 7.1 AVR + Dahlquist 350W 8" Sub + Kl... 
  hide details  
Reply
CHILZ - Lan Rig
(17 items)
 
CANARY - Main Rig
(16 items)
 
CADILLAC - HTPC
(14 items)
 
CPUMotherboardGraphicsRAM
Intel i5-4570s Asus H97M-Plus AMD R9 280 G.Skill RipjawsX 16 GB (2x8) 
Hard DriveHard DriveHard DriveCooling
250 GB Samsung 840 240 GB Kingston 3 TB USB 3.0 Drive Custom Loop 
OSMonitorKeyboardPower
Windows 10 Preview BenQ GL2450 Filco MajesTouch2 Ninja PC P&C Silencer Mk III 600 W 
CaseMouseMouse PadAudio
Fractal Design Core 1000 Mionix Castor Monoprice XXL JL Amps + Custom Morel Bookshelf speakers 
Other
Scythe Kama-Panel 3 
CPUMotherboardGraphicsRAM
FX-8150 @ 4.6 GHz Fatal1ty 990FX Pro 9800 GTX+ 512 MB G.Skill Ripjaws X 1866 CL9 
Hard DriveHard DriveCoolingOS
120 GB OCZ Vertex 3 1 TB WD Black 5x 120mm + MCP350 + EK Supreme HF + MicroRes Windows 8 Consumer Preview 
MonitorKeyboardPowerCase
2x Dell U2212HM Logitech G110 Cooler Master 850W Silent Pro Cooler Master 690 II Adv. 
MouseMouse PadAudio
Razer Death Adder 3.5G Staples Gel Cushion Asus Xonar DG + Senn. PC333D 
CPUMotherboardGraphicsRAM
Q6600 Acer X1800 ATI 5670 2 GB Kingston 
Hard DriveOptical DriveOSMonitor
2 TB WD Green Asus BD-R Windows 7 Home Premium Sony 50" LCD 
KeyboardPowerCaseMouse
Acer Media 220 W SFF Acer X1800 Acer Optical 
Mouse PadAudio
The TV cabinet Denon 2808 7.1 AVR + Dahlquist 350W 8" Sub + Kl... 
  hide details  
Reply
post #6 of 6
Or even better yet, use prepared statements: http://php.net/manual/en/pdo.prepared-statements.php
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Web Coding
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Web Coding › PHP/MySQL Help (Inserting Values into multiple tables.