|
PHP - Insert From 1 Table To 2 Tables
Hi,
I'm quite new to php and not the greatest of coders, so urgently require your help. I want to be able to take data from an un-normalised table and load into 2 normalised tables as below: 1) Table 1 - All Music Tracks (Un-normalised table) Album Name Artist Name Track Name Track Length etc. 2) Table 2 - Albums Table (Normalised table) ID Artist Name Album Name etc. 3) Table 3 - Tracks Table (Normalised table) ID Track Name Album ID (foreign key to Albums table) etc. Can someone please provide some sample code as a starting point? The All Music Tracks table includes both the album name and track name in each row, but I only want the album appearing once in the Albums tables, would I have to do an Distinct select first to get the album names into an array? I'm not sure how I would then have nested loop to do the insert into the Tracks table. Much Appreciated, PD Similar TutorialsI have 3 tables w/ the following fields: Cities id | state_id | city_name States id | state_name New_cities id | state_id | city_name I am trying to write a script (and still working on it) that will INSERT new cities in cities WHERE New_cities.state_id = Cities.state_id. Obviously I am still in testing and results sort out, but honest to all hell, I might be going about this all wrong, and would appreciate any comments, criticism, assistance or feedback on what I am trying to do. <?php $query = "SELECT * FROM cities, states, new_cities"; if ($results = mysqli_query($cxn, $query)) { $row_cnt = mysqli_num_rows($results); echo $row_cnt . " Total Records in Query.<br /><br />"; if (mysqli_num_rows($results)) { while ($row = mysqli_fetch_array($results)) { if ($row['cities']['state_id'] == $row['new_cities']['new_state_id']) { $insert_city_query = "INSERT INTO cities id, new_state_id, city_name VALUES ('','$new_state_id','$city_name')" or mysqli_error(); } //if ($row['cities']['state_id'] == $row['new_cities']['new_state_id']) echo "$row[new_city_name]<br />"; } //while ($row = mysqli_fetch_array($results)) } //if (mysqli_num_rows($results)) } //if ($results = mysqli_query($cxn, $query)) ?> I have a form that inserts a tables records into my database. Ive tried to modify it so it will insert into two tables but it only seems to insert into one table and not the other. I made a query that counts the rows of the 2nd table and +1, which is then suppose to be inserted. Code: [Select] <?php $editFormAction = $_SERVER['PHP_SELF']; if (isset($_SERVER['QUERY_STRING'])) { $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']); } if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) { $insertSQL = sprintf("INSERT INTO fleet (FleetName) VALUES (%s)", GetSQLValueString($_POST['ffleetname'], "text")); $insertSQL = sprintf("INSERT INTO ships (ShipName, FleetName, PlayerName, Image, Image2, Cost, HealthA, HealthB, MaxHealthA, MaxHealthB, `Class`, PlanetName) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", GetSQLValueString($_POST['shipname'], "text"), GetSQLValueString($_POST['fleetname'], "text"), GetSQLValueString($_POST['playername'], "text"), GetSQLValueString($_POST['image1'], "text"), GetSQLValueString($_POST['image2'], "text"), GetSQLValueString($_POST['cost'], "int"), GetSQLValueString($_POST['ha'], "int"), GetSQLValueString($_POST['hb'], "int"), GetSQLValueString($_POST['mha'], "int"), GetSQLValueString($_POST['mhb'], "int"), GetSQLValueString($_POST['class'], "int"), GetSQLValueString($_POST['select'], "text")); mysql_select_db($database_swb, $swb); $Result1 = mysql_query($insertSQL, $swb) or die(mysql_error()); $insertGoTo = "ship.php"; if (isset($_SERVER['QUERY_STRING'])) { $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?"; $insertGoTo .= $_SERVER['QUERY_STRING']; } header(sprintf("Location: %s", $insertGoTo)); } $colname_Fleetcount = "-1"; if (isset($_GET['recordID'])) { $colname_Fleetcount = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']); } mysql_select_db($database_swb, $swb); $query_Fleetcount = sprintf("SELECT * FROM fleet"); $Fleetcount = mysql_query($query_Fleetcount, $swb) or die(mysql_error()); $row_Fleetcount = mysql_fetch_assoc($Fleetcount); $totalRows_Fleetcount = mysql_num_rows($Fleetcount); ?> <input name="image1" type="hidden" value="<?php echo $row_Ships['Image']; ?>" /> <input name="image2" type="hidden" value="<?php echo $row_Ships['Image2']; ?>" /> <input name="cost" type="hidden" value="<?php echo $row_Ships['Cost']; ?>" /> <input name="ha" type="hidden" value="<?php echo $row_Ships['HealthA']; ?>" /> [b] <input name="ffleetname" type="hidden" value="<?php echo $totalRows_Fleetcount = mysql_num_rows($Fleetcount) + 1;?>" /> <input name="fleetname" type="hidden" value="<?php echo $totalRows_Fleetcount = mysql_num_rows($Fleetcount) + 1;?>" />[/b] <input name="hb" type="hidden" value="<?php echo $row_Ships['HealthB']; ?>" /> <input name="mha" type="hidden" value="<?php echo $row_Ships['MaxHealthA']; ?>" /> <input name="mhb" type="hidden" value="<?php echo $row_Ships['MaxHealthB']; ?>" /> <input name="class" type="hidden" value="<?php echo $row_Ships['Class']; ?>" /> <input name="playername" type="hidden" value="<?php echo $_SESSION['MM_Username']; ?>" /> <input name="shipname" type="hidden" value="<?php echo $row_Ships['ShipName']; ?>" /> <input type="submit" name="Submit" value="Submit" /> <input type="hidden" name="MM_insert" value="form1"> </form> Any ideas? Thanks Quote $con = mysql_connect("localhost","****","****"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("checkmyw_database", $con); mysql_query("INSERT INTO requests (username, password, email, weavecode, mobile, jobtitle, relationship, sexuality, venue, type) VALUES ('$me', '$pw', '$email','1999','$mobile','$job','$relationship','$sexuality','$venue','newuser')"); mysql_query("INSERT INTO members (username, password, email, weavecode, mobile, jobtitle, relationship, sexuality,) VALUES ('$me', '$pw', '$email','1999','$mobile','$job','$relationship','$sexuality',)"); mysql_close($con); any idea why this doesn't work? Hi guys, Is it possible to insert into two tables in a database? Thanks Hi
I am very new to PHP & Mysql.
I am trying to insert values into two tables at the same time. One table will insert a single row and the other table will insert multiple records based on user insertion.
Everything is working well, but in my second table, 1st Table ID simply insert one time and rest of the values are inserting from 2nd table itself.
Now I want to insert the first table's ID Field value (auto-incrementing) to a specific column in the second table (only all last inserted rows).
Ripon.
Below is my Code:
<?php
$con = mysql_connect("localhost","root","aaa");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("ccc", $con);
$PI_No = $_POST['PI_No'];
$PO_No = $_POST['PO_No'];
$qry = "INSERT INTO wm_order_entry (
Order_No,
PI_No,
PO_No)
VALUES(
NULL,
'$PI_No',
'$PO_No')";
$result = @mysql_query($qry);
$val1=$_POST['Size'];
$val2=$_POST['Style'];
$val3=$_POST['Colour'];
$val4=$_POST['Season_Code'];
$val5=$_POST['Dept'];
$val6=$_POST['Sub_Item'];
$val7=$_POST['Item_Desc'];
$val8=$_POST['UPC'];
$val9=$_POST['Qty'];
$N = count($val1);
for($i=0; $i < $N; $i++)
{
$profile_query = "INSERT INTO order_entry(Size,
Style,
Colour,
Season_Code,
Dept,
Sub_Item,
Item_Desc,
UPC,
Qty,
Order_No
) VALUES( '$val1[$i]','$val2[$i]','$val3[$i]','$val4[$i]','$val5[$i]','$val6[$i]','$val7[$i]','$val8[$i]','$val9[$i]',LAST_INSERT_ID())";
$t_query=mysql_query($profile_query);
}
header("location: WMView.php");
mysql_close($con);
?>
Output is attached.hello, anybody able to see what im doing wrong? Code: [Select] $id2=mysql_insert_id(); $year=$_POST['y']; $yr=substr($year,-2); $mth=$_POST['m']; $init=$_POST['initial']; $jobnumber=$yr.$mth.'-'.$id2.$init; $query = "INSERT INTO jobs VALUES ( '', '$id2', '$_POST[initial]', '$_POST[y]-$_POST[m]-$_POST[d]',date '$_POST[contact]', '$_POST[contactphone]', '$_POST[customer]', '$_POST[address]', '$_POST[city]', '$_POST[postal]', '$_POST[province]', '$_POST[description]' )"; mysql_query($query) or die('Error, adding new job failed. Check you fields and try again.'); echo "You have successfully entered a new job. The job number is $jobnumber"; Hi there, I have a form which I want to submit data into my tables. There are going to be 4 tables involved with this form, and these 4 tables should relate to one another in some sort of way. My problem is either PHP or MySQL, but I keep getting a warning which I can't figure out. I remember this warning appearing even if the code before it is wrong, therefore I am not relying on it. This is what the error says: Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in G:\xampp\htdocs\xampp\dsa\wp3.php on line 40 Here's my code: Code: [Select] <html> <head> <title>WP3</title> </head> <body> <form id="search" name="search" id="search" method="get" action="searchresults.php" /> <input type="text" name="terms" value="Search..." /> <input class="button" type="submit" name="Search" value="Search" /> </form> <?php include_once('connectvars.php'); $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); if(isset($_POST['report'])){ $firstname = mysqli_real_escape_string($dbc, trim($_POST['firstName'])); $middlename = mysqli_real_escape_string($dbc, trim($_POST['middleName'])); $lastname = mysqli_real_escape_string($dbc, trim($_POST['lastName'])); $image = mysqli_real_escape_string($dbc, trim($_POST['image'])); $phone = mysqli_real_escape_string($dbc, trim($_POST['phone'])); $organisation = mysqli_real_escape_string($dbc, trim($_POST['organisation'])); $street = mysqli_real_escape_string($dbc, trim($_POST['street'])); $town = mysqli_real_escape_string($dbc, trim($_POST['town'])); $city = mysqli_real_escape_string($dbc, trim($_POST['city'])); if (!empty($firstname) && !empty($middlename) && !empty($lastname) && !empty($image) && !empty($phone) && !empty($organisation) && !empty($city)) { $query = "INSERT INTO report (organisation, phoneNo) VALUES ('$organisation', '$phone'); INSERT INTO person (firstName, middleName, lastName) VALUES ('$firstname', '$middlename', '$lastname'); INSERT INTO identification (image) VALUES ('$image'); INSERT INTO location (street, town, city) VALUES ('$street', '$town', '$city')"; $data = mysqli_query($dbc, $query); if (mysqli_num_rows($data) == 0) { mysqli_query($dbc, $query); echo "Thank you, your report has been received."; } else { // An account already exists for this username, so display an error message echo '<p>This report already exists.</p>'; $username = ""; } } else echo "Please enter all of the fields"; } ?> <form id="report_sighting" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"> <h2>Report a sighting</h2> <table> <tr> <td> <label>First name:</label> </td> <td> <input type="text" id="firstname" name="firstName" value="<?php if (!empty($firstname)) echo $firstname; ?>" /> </td> </tr> <tr> <td> <label>Middle name:</label> </td> <td> <input type="text" id="middlename" name="middleName" value="<?php if (!empty($middlename)) echo $middlename; ?>" /> </td> </tr> <tr> <td> <label>Last name:</label> </td> <td> <input type="text" id="lastname" name="lastName" value="<?php if (!empty($lastname)) echo $lastname; ?>" /> </td> <tr> <td> <label>Upload Identification:</label> </td> <td> <input type="file" id="image" name="image" /> </td> <tr> <tr> <td> <label>Contact phone number: </label> </td> <td> <input type="text" id="phone" name="phone" /> </td> <tr> <tr> <td> <label>Organisation: </label> </td> <td> <input type="text" id="organisation" name="organisation" /> </td> </tr> <tr> <td> <label>Street seen: </label> </td> <td> <input type="text" id="street" name="street" /> </td> </tr> <tr> <td> <label>Town seen: </label> </td> <td> <input type="text" id="town" name="town" /> </td> </tr> <tr> <td> <label>City seen: </label> </td> <td> <input type="text" id="city" name="city" /> </td> </tr> <tr> <td> </td> <td> <input type="submit" value="Report" name="report" /> </td> </tr> </table> </form> </body> </html> I've checked out the SQL statement and it's alright, so that leaves me with the PHP. I would very much appreciate if anyone could help me out here, thanks. Hey guys, it seems that so far in my little script everything is working except for the MySQL insert Nothing seems to appear in the table it's supposed to insert too but i dont see any errors anywhere. Any help would be appreciated. I know it's very messy but it's my first actual 'project' Code: [Select] <?php $apiKey="<omitted>"; $playeritemsURL="http://api.steampowered.com/IEconItems_440/GetPlayerItems/v0001/"; $fullURL=$playeritemsURL."?key=".$apiKey."&SteamID=".$_GET["SteamID64"]."&format=xml"; $playerLocalName=$_GET["SteamID64"].".xml"; $ch=curl_init($fullURL); $fh=fopen($playerLocalName, 'w'); ?> You entered ID: <?php echo $_GET["SteamID64"];?></br> <?php echo $fullURL; curl_setopt($ch,CURLOPT_FILE,$fh); curl_exec($ch); curl_close($ch); ?> </br> <?php $con = mysql_connect("localhost","root",""); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("csv_db", $con); $readXML=file_get_contents($playerLocalName); $xml=simplexml_load_file($playerLocalName); $output=$_GET["SteamID64"].".defindex.txt"; $fh=fopen($output, 'w') or die("can't open file"); //echo $xml->items->item->defindex[0]; foreach ($xml->xpath('//defindex') as $defindex) { fwrite($fh, $defindex."\n"); echo $defindex."</br>"; mysql_query("INSERT INTO ".$_GET["SteamID64"]." (defindex) VALUES (".$defindex.")"); } ?> Hello guys, this is my first post so sorry if I made any mistake
I need select record from one table and move to another table
But I get this message saying "Warning: mysqli_query() expects at least 2 parameters, 1 given in" I had that on line 158, but now i get on line 156
I start to do PHP and mysql few weeks ago, only respond i get from teacher is search and search.
<?php
if (isset($_POST['username']))
{
$searchq = $_POST['username'];
mysqli_query("SELECT * FROM login WHERE username='$searchq'")or die ("could not search");
while($row = mysqli_fetch_array($con, $query))
{
$username = $row['username'];
$password = $row['password'];
$age = $row['age'];
$phonenumber = $row['phonenumber'];
$nationality = $row['nationality'];
mysqli_query("INSERT INTO admin SET username ='$username', password='$password', age='$age', phonenumber='$phonenumber', nationality='$nationality'" ) ;
echo"Data successfully inserted";
}
}
?>
When i search i see this type of code "$data = mysqli_query" add variable before mysqli
What should I do, to make it work. And send record from one table to another. Thank you
I am trying to insert records from an foreach array, it only inserts the first record and I cannot work out how to get it to loop through all of the records. Thank you foreach($_SESSION['post_data_array'] AS $seat) { $rowId = substr($seat, 0, 1); $columnId = substr($seat, 1); echo $rowId . $columnId . ", "; } $sql125 = "INSERT INTO booked_seats(booking_id, row_id, column_id) values ('$book_id', '$rowId', '$columnId')"; $result125 = mysql_query($sql125); if ($result125) { echo "worked"; } else { echo "didnt work"; } Im trying to insert some values automatically into a table once the form loads, but Im getting an error. Here is the code Code: [Select] <?php $aid = $_GET['aid']; $sd = $_GET['sd']; ?> <style> #message {margin:20px; padding:20px; display:block; background:#cccccc; color:#cc0000;} </style> <div id="message">Your notification has been submitted.</div> <div style="text-align:center "> <?php $connection = mysql_connect("localhost", "username", "password"); mysql_select_db("articles", $connection); $query="INSERT INTO broken_links (articleid, article) VALUES ('$aid', '$sp')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "Submitted"; mysql_close($con) ?> <table border="0" cellpadding="3" cellspacing="3" style="margin:0 auto;" > <input type="submit" id="Login" value=" Thank you. Please press to close " onclick="tb_remove()"></td> </tr> </table> </div> Any help will be appreciated Code: [Select] $myusername = $_POST['myusername']; mysql_query("INSERT INTO logedin ( username ) VALUES ('$myusername')") OR die("Could not send the message: <br>".mysql_error()); wont insert anything no error's given even just trying to insert username into the the loged in table yes i know i spelt loged in wrong but i did that on the db lol Hi, I would like to do the following in PHP using a MySQL database, but not sure how. select a, b,c etc.. from mailbox where id = X then insert id,a,b,c etc.. into problem When I select, I will be calling the id So I would like it to get data from mailbox and then insert it into problem. How would I go about doing this? Thanks for any help you can give me Hello, I'm working on a PHP register form, all I want to do, is to be able to insert an extra variable into the database: Code: [Select] // now we insert it into the database $insert = " INSERT INTO users (username, password) VALUES ('".$_POST['username']."', '".$_POST['pass']."') "; $add_member = mysql_query($insert); right, so I have the username and password variable being passed. But I also want to pass a variable into a column called 'weaponAttachments', all I want for this variable to be is 5000. So, in other words, something like this: 5000['weaponAttachments'] Thanks Hi,
The following code was written by someone else. It allows me to upload images to a directory while saving image name in the mysql table.
I also want the code to allow me save other data (surname, first name) along with the image name into the table, but my try is not working, only the images get uploaded.
What am I missing here?
if(isset($_POST['upload']))
{
$path=$path.$_FILES['file_upload']['name'];
if(move_uploaded_file($_FILES['file_upload']['tmp_name'],$path))
{
echo " ".basename($_FILES['file_upload']['name'])." has been uploaded<br/>";
echo '<img src="gallery/'.$_FILES['file_upload']['name'].'" width="48" height="48"/>';
$img=$_FILES['file_upload']['name'];
$query="insert into imgtables (fname,imgurl,date) values('$fname',STR_TO_DATE('$dateofbirth','%d-%m-%y'),'$img',now())";
if($sp->query($query)){
echo "<br/>Inserted to DB also";
}else{
echo "Error <br/>".$sp->error;
}
}
else
{
echo "There is an error,please retry or check path";
}
}
?>
joseph
Hi guys, Im making an event booking/reservation site, I have 5 tables (Malls, Events, Booking, Reservation and Users) which are all connected and doing fine. I created a function which is this... function select_all_events_by_mall_id($mall_id) { echo "<table id='event_table'>"; echo "<th>Event Name</th>"; echo "<th>Event Location</th>"; echo "<th>Number of Cars</th>"; echo "<th>Description</th>"; echo "<th>Booked</th>"; echo "<th>Reserved</th>"; echo "<th>Reserved Until</th>"; echo "<th>Vacant</th>"; $events_set = select_all_events($mall_id); while($events = mysql_fetch_array($events_set)) { echo "<tr>"; echo "<td>" . $events['event_name'] . "</td>"; echo "<td>" . $events['event_location'] . "</td>"; echo "<td>" . $events['number_of_cars'] . "</td>"; echo "<td>" . $events['description'] . "</td>"; // Booked echo "<td>"; $booked_set = select_all_booking($events['id']); while($booked = mysql_fetch_array($booked_set)) { $user_set = select_all_user_booked($booked['users_id']); while($user = mysql_fetch_array($user_set)) { echo $user['company'] . ", " . $user['branch'] . "<br />"; } } echo "</td>"; // Reserved echo "<td>"; echo "<ol>"; $reserved_set = select_all_reservation($events['id']); while($reserved = mysql_fetch_array($reserved_set)) { if(empty($reserved['events_id'])) { echo "No Reservation's "; } else { $reserved_user = select_all_user_reserved($reserved['users_id']); while($user_set = mysql_fetch_array($reserved_user)) { echo "<li>" . $user_set['brand'] . ", " . $user_set['branch'] . "</li>"; } } } echo "</ol>"; echo "</td>"; // Expiration Date echo "<td>"; echo "<ol>"; $reserved_set = select_all_reservation($events['id']); while($reserved = mysql_fetch_array($reserved_set)) { $reserved_user = select_all_user_reserved($reserved['users_id']); while($user_set = mysql_fetch_array($reserved_user)) { $exp_date = select_all_expiration_date($user_set['id']); while($date = mysql_fetch_array($exp_date)) { echo "<li>" . $date['expiration_date'] . "</li>"; } } } echo "</ol>"; echo "</td>"; //Vacant echo "<td>"; $vacant_set = select_all_booking($events['id']); $booking_count = mysql_num_rows($vacant_set); $reserved_count = mysql_num_rows($reserved_set); $total_count = $booking_count + $reserved_count; $vacant = $events['number_of_cars'] - $total_count; if($vacant < 0) { echo "This Event is fully booked"; } else { echo $vacant; } echo"</td>"; echo "</tr>"; } echo "</table>"; } The function starts by receiving an id from picking a mall name from a select tag. It works fine, it outputs the data on the page like i want it to, but my problem is that i need a way to add a submit or any button to each generated row that will, when clicked will insert the event id and user id (who clicked it) to my booking/reservation table as a new row. I tried adding a form inside my function and saving the event id every loop but when i click the submit button i think it doesn't work (or i made a mistake).. Sorry if my coding sucks, I'm kinda new in php. Sorry if its kinda confusing my head is spinning right now from thinking this stuff the whole day.. Thanks in advance for any comments and ideas. Hi everyone, wondering if any one could help me, probably simple to most. I am using an API from a video game and I am trying to insert the xml data I get back in to a MYSQL database table. but can't seem to figure it out. I created a simple echo foreach row which works, but I cannot seem to figure it out how to insert in to DB table. also the rows are attributes don't know if that makes a difference? Code: [Select] xml = simplexml_load_file('file.xml'); foreach ($xml->result->rowset->row as $row) { echo '<tr class="' . $row['ownerName2'] . '">' . '<td>' . $row['date'] . '</td>' . '<td>' . $row['ownerName1'] . '</td>' . '<td>' . $row['ownerName2'] . '</td>' . '<td>' . $row['amount'] . '<b>ISK</b>' . '</td>' . '<td>' . $row['balance'] . '<b>ISK</b></td>' . '<td>' . $row['reason'] . ' </td>' . '</tr>'; }; This is the structure of the xml document if you need to have a look. Code: [Select] <result> <rowset name="entries" key="refID" columns="date,refID,refTypeID,ownerName1,ownerID1,ownerName2,ownerID2,argName1,argID1,amount,balance,reason"> <row date="2011-09-16 14:19:00" refID="4873095949" refTypeID="37" ownerName1="name" ownerID1="435353535" ownerName2="name" ownerID2="2342523523" argName1="name" argID1="325235252" amount="-1101525.00" balance="0.00" reason="DESC:"/> <row date="2011-09-16 14:19:00" refID="4873095949" refTypeID="37" ownerName1="name" ownerID1="435353535" ownerName2="name" ownerID2="2342523523" argName1="name" argID1="325235252" amount="-1101525.00" balance="0.00" reason="DESC:"/> <row date="2011-09-16 14:19:00" refID="4873095949" refTypeID="37" ownerName1="name" ownerID1="435353535" ownerName2="name" ownerID2="2342523523" argName1="name" argID1="325235252" amount="-1101525.00" balance="0.00" reason="DESC:"/> </rowset> </result> Thanks in advance. Im trying to insert a name into my database but I need it to go into the column that matches the sessions user id Code: [Select] "INSERT INTO users SET name = '".$_POST['name']."'"; how do I tell it to put name into name column with id matching Session id. really appreicate if someone could please help me with this. |
|||||||