|
PHP - Getting Date And Updating Multiple Tables.
As I am new to all this php, I need some help and/or ideas on how to code this little idea of mine:
I have a database with a members table, I would like to create some Teams, and so I want too create a new table with the info for these teams. My problem is to set what team a member belongs too. My idea short Add a new column in the members area to hold the Id of the team this member belongs too. So if member 1000 sends a "join team link" too member 2000 the script needs to get the team id from member 1000 and add this id to member 2000. select teamid from members where id=1000 update teamid='same id as member 1000' where id='membersid' I hope this all makes sense, cause I am having a hard time trying to explain it. Similar TutorialsOk, so I'm not quite sure how to explain this, but here it goes: I have table A that contains stats for all players in the NHL, and then I have table B with just a few players. These few players in table B are also in table A, but table B has more information on them. I want to take the stats for these players out of table A and put into table B, and I want table B to update along with table A every time those numbers change. How would I do this? I am trying to update last logged in entry in the database upon succesful login. I may be way off in the logic here or I may be missing something simple. I don't get any errors and it logs in fine. Just does not update the lastvisit field in the database. Code: [Select] //record date of most recent login $result = mysql_query("SELECT username FROM users WHERE user_id ='".$_SESSION['userId'] . "'"); $dtCreated = date('Y-m-d'); mysql_query("UPDATE users SET lastvisit=('$dtCreated') WHERE username = $result"); I'm making an amendment to my login page so that when a user logs in, the date_last_login field in the members table is updated to NOW(). Well, that's the idea.
The login works fine. Always has. It's just this update that isn't happening. No error message appears, but date_last_login remains 0000-00-00 00:00:00 . Any tips?
$sql_date_last_login = $pdo->exec("update members set date_last_login = now() where member_id = '".$SESSION['member_id']."'");
Cheers,
Dave
Hi all I have 3 tables Table_1, Table_2 and Table_3 Table_1 is a list of countries, with name and country_id Table_2 is a table that has 3 fields, id, name and description Table 3 is a table that has name, Table_2_id So what I need to do: Display the name and description field of Table_2 in a form Loop through the countries table and display each as an input box and display on the same form When I fill out the form details, the name/description must be inserted into Table_2, creating an id The input boxes data then also needs inserting into Table_3, with the foreign key of Table_2_id So a small example would be: Name: testing Description: this is a test Country of Australia: Hello Country of Zimbabwe: Welcome This means that in Table_2, I will have the following: ============================= | id | name | description | 1 | testing | this is a test ============================= Table_3 ============================= | Table_2_id | name | country_id | 1 | Hello | 20 | 1 | Welcome | 17 ============================= 20 is the country_id of Australia 17 is the country_id of Zimbabwe Code: Generating the input fields dynamically: $site_id = $this->settings['site_id']; $options = ''; $country_code = ''; $query = $DB->query("SELECT country_code, country_id, IF(country_code = '".$country_code."', '', '') AS sel FROM Table_1 WHERE site_id='".$this->settings['site_id']."' ORDER BY country_name ASC"); foreach ($query->result as $row) { $options .= '<label>' . 'Test for ' . $this->settings['countries'][$row['country_code']] . '</label>' . '<br />'; //$row['country_id'] is the country_id from Table_1 $options .= '<input style="width: 100%; height: 5%;" id="country_data" type="text" name="' . $row['country_id'] . '" value="GET_VALUE_FROM_DB" />' . '<br /><br />'; } echo $options; This outputs: Code: [Select] Textareas go here...... <label>Test for Australia</label> <input type="text" value="" name="20" id="country_data" style="width: 100%; height: 5%;"> <label>Test for Zimbabwe</label> <input type="text" value="" name="17" id="country_data" style="width: 100%; height: 5%;"> Now, I need to insert the value of the input field and it's country_id (20 or 17) into Table_3 and also Table_2_id. This then means I could get the value from Table_3 to populate 'GET_VALUE_FROM_DB' But I'm at a loss on how I'd do this. Could someone help me with this? Thanks
Hi Guys,
<?php
$host="xxx"; // Host name
$username="xxx"; // Mysql username
$password="xxx"; // Mysql password
$db_name="xxx"; // Database name
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql="SELECT * FROM attendance";
$result=mysql_query($sql);
// Count table rows
$count=mysql_num_rows($result);
?>
<html>
<head>
<title>Registers</title>
</head>
<body>
<?php include 'Navigation.php';?>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<table border="0" cellspacing="1" cellpadding="0" style="width: 1461px; height: 105px">
<tr>
<td align="center"><strong>Id</strong></td>
<td align="center"><strong>Last Name</strong></td>
<td align="center"><strong>First Name</strong></td>
<td align="center"><strong>Form</strong></td>
<td align="center"><strong>Year Group </strong></td>
<td align="center"><strong>Date</strong></td>
<td align="center"><strong>Attendance</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $Student_id[]=$rows['Student_id']; ?><? echo $rows['Student_id']; ?></td>
<td align="center"><? $firstname[]=$rows['firstname']; ?><? echo $rows['firstname']; ?></td>
<td align="center"><? $lastname[]=$rows['lastname']; ?><? echo $rows['lastname']; ?></td>
<td align="center"><? $Form_Group[]=$rows['Form_Group']; ?><? echo $rows['Form_Group']; ?></td>
<td align="center"><? $Year_Group[]=$rows['Year_Group']; ?><? echo $rows['Year_Group']; ?></td>
<td align="center"><? $Att_Date[]=$rows['Att_Date']; ?><? echo $rows['Att_Date']; ?></td>
<td align="center"><input name="Presence[]" type="text" id="Presence" value="<? echo $rows['Presence']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="7" align="center"><input type="submit" name="Submit" value="Submit Register"></td>
</tr>
</table>
</form>
</table>
<?php
// Check if button name "Submit" is active, do this
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE attendance SET Presence='$Presence[$i]' WHERE Student_id='$Student_id[$i]'";
$result1=mysql_query($sql1);
}
}
if($result1){
header("location:Registers.php");
}
mysql_close();
?>
Hi,
I am trying to create an admin page for a local Gym Club to allow an Administrator to be able to update club prices which then show on different screens on the site.
I have been able to display the "Prices Admin" page which basically reads a MySQL database table (pricelist), display the description, member price and non-member price and allows the user to update any of these fields.
My problem is that when I try writing the data back to the database with the "UPDATE" statement it fails, what I mean is that nothing updates. I have at the moment commented out the actual update statement and put in a "file_put_contents" command" to try and work out what is in the various fields before the UPDATE is run. I find that the display/INPUT works perfectly well but when I do a foreach on the $record array variable I am getting strange results.
I may not have explained this very well but here are the relevant sections of my code;
// Display and Input section
<div id="admin-area"> Hi All, Can't get this code working. the statement works fine directly on the table, just not when executed from the PHP file: Code: [Select] <strong>Multi Update</strong><br> <?php $host="localhost"; // Host name $username="user_name"; // Mysql username $password="Password"; // Mysql password $db_name="dbname"; // Database name $tbl_name="tablename"; // Table name // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $sql="SELECT mik1vm_product.product_id, mik1vm_product.product_sku, mik1vm_product.product_name, mik1vm_product.product_in_stock, mik1vm_product.product_in_stock_cardiff FROM $tbl_name"; $result=mysql_query($sql); // Count table rows $count=mysql_num_rows($result); ?> <table width="500" border="0" cellspacing="1" cellpadding="0"> <form name="form1" method="post" action=""> <tr> <td> <table width="500" border="0" cellspacing="1" cellpadding="0"> <tr> <td align="center"><strong>Product Id</strong></td> <td align="center"><strong>SKU</strong></td> <td align="center"><strong>Name</strong></td> <td align="center"><strong>In Stock</strong></td> <td align="center"><strong>In Stock Cardiff</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td align="center"><? $product_id[]=$rows['product_id']; ?><? echo $rows['product_id']; ?></td> <td align="center"><input name="product_sku[]" type="text" id="product_sku" value="<? echo $rows['product_sku']; ?>"></td> <td align="center"><input name="product_name[]" type="text" id="product_name" value="<? echo $rows['product_name']; ?>"></td> <td align="center"><input name="product_in_stock[]" type="text" id="product_in_stock" value="<? echo $rows['product_in_stock']; ?>"></td> <td align="center"><input name="product_in_stock_cardiff[]" type="text" id="product_in_stock_cardiff" value="<? echo $rows['product_in_stock_cardiff']; ?>"></td> </tr> <?php } ?> <tr> <td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td> </tr> </table> </td> </tr> </form> </table> <?php // Check if button name "Submit" is active, do this if($Submit){ for($i=0;$i<$count;$i++){ $sql1="UPDATE $tbl_name SET product_sku='$product_sku[$i]', product_name='$product_name[$i]', product_in_stock='$product_in_stock[$i]' , product_in_stock_cardiff='$product_in_stock_cardiff[$i]' WHERE product_id='$product_id[$i]'"; $result1=mysql_query($sql1); } } if($result1){ header("location:test.php"); } mysql_close(); ?> The page loads fine, but just doesn't update the DB. Thanks in advance for any help. I have a problem with an amend to an admin system I am trying to change. The shop stocks different colours of each product and the client wants to be able to update the stock levels of all colours of one product (which all share the same title) on one form. I have tried following some instructions on some sites and tried making my own and I can get it to display the form and the values correctly, but on submission it does nothing but direct me to a page without a title value in the address (e.g. www.example.com/example/stock_level_2.php?title=). I think it may be to do with the arrays I am using, has anyone got any ideas? Here is the code. Form: Code: [Select] <?php $pd_title = str_replace("_"," ",$_REQUEST["title"]); $pd_title = str_replace("%","&#37;",$pd_title); $pd_title = str_replace("/","&#47;",$pd_title); $lookupqueryc = mysql_query("SELECT * FROM product WHERE pd_title = '".$pd_title."' ORDER BY pd_id DESC"); echo '<table><tr><th align="center" class="bodytext">Product<br />ID</th><th align="center" class="bodytext">Product Name</th><th align="center" class="bodytext">Stock Level</th></tr>'; $form = '<form name="frmAmendStock" method="post" action="stock_level_2.php?title="'; $form .= $_GET["title"]; $form .= '" enctype="multipart/form-data">'; echo $form; echo '<input name="submitted" type="hidden" value="true">'; $count=mysql_num_rows($lookupqueryc); echo '<input name="count" type="hidden" value="'; echo $count; echo '">'; while($lookupresultc = mysql_fetch_array($lookupqueryc)){ echo '<tr><td align="center" class="bodytext">'; $id[]=$lookupresultc["pd_id"]; echo $lookupresultc["pd_id"]; echo '</td><td class="bodytext">'; echo $lookupresultc["pd_title"]; if (isset($lookupresultc["pd_colour"]) && $lookupresultc["pd_colour"] !="") { echo " ("; echo $lookupresultc["pd_colour"]; echo ')'; } if ($lookupresultc["pd_stock"] <= 5) { echo '</td><td align="center" class="bodytext" style="border: 2px solid red;">'; echo '<input name="stock[]" type="text" id="stock" value="'; echo $lookupresultc["pd_stock"]; echo '"style="width: 50px;" />'; echo "</td></tr>"; }else{ echo '</td><td align="center" class="bodytext">'; echo '<input name="stock[]" type="text" id="stock" value="'; echo $lookupresultc["pd_stock"]; echo '"style="width: 50px;" />'; echo "</td></tr>"; } } ?> <tr> <td> </td> <td height="35"><a href="javascript: " onClick="javascript:document.forms.frmAmendStock.submit()" class="bodytext"><strong>click here to amend your stock </strong></a></td> </tr> </form> </table> And here is the code it should run on submission: Code: [Select] <?php if (isset($_POST["submitted"])) { for($i=0;$i<$_POST["count"];$i++){ mysql_query("UPDATE product SET pd_stock='$stock[$i]' WHERE pd_id='$id[$i]'"); } } ?> I have a form where a user can duplicate part of a form like below: I've named the fields with the HTML arrays (ie. "fieldName[]") and came up with this: //--> CONTROLLERS $system_controllers_qty = $_POST['system_controllers_qty']; $system_controllers_make = $_POST['system_controllers_make']; $system_controllers_model = $_POST['system_controllers_model']; $system_controllers_serial_no = $_POST['system_controllers_serial_no']; for ($i = 0; $i < count($system_controllers_qty); $i++) { echo "qty " . clean($system_controllers_qty[$i]) . "<br/>"; echo "make " . clean($system_controllers_make[$i]) . "<br/>"; echo "model " . clean($system_controllers_model[$i]) . "<br/>"; echo "serial_no " . clean($system_controllers_serial_no[$i]) . "<br/>"; } //--> CPUS $system_cpus_qty = $_POST['system_cpus_qty']; $system_cpus_model = $_POST['system_cpus_model']; $system_cpus_serial_no = $_POST['system_cpus_serial_no']; $system_cpus_speed = $_POST['system_cpus_speed']; for ($i = 0; $i < count($system_cpus_qty); $i++) { echo "qty " . clean($system_cpus_qty[$i]) . "<br/>"; echo "model " . clean($system_cpus_model[$i]) . "<br/>"; echo "serial_no " . clean($system_cpus_serial_no[$i]) . "<br/>"; echo "speed " . clean($system_cpus_speed[$i]) . "<br/>"; } //--> DISKS $system_disks_qty = $_POST['system_disks_qty']; $system_disks_make = $_POST['system_disks_make']; $system_disks_model_no = $_POST['system_disks_model_no']; $system_disks_size = $_POST['system_disks_size']; $system_disks_serial_no = $_POST['system_disks_serial_no']; for ($i = 0; $i < count($system_disks_qty); $i++) { echo "qty " . clean($system_disks_qty[$i]) . "<br/>"; echo "make " . clean($system_disks_make[$i]) . "<br/>"; echo "model_no " . clean($system_disks_model_no[$i]) . "<br/>"; echo "size " . clean($system_disks_size[$i]) . "<br/>"; echo "serial_no " . clean($system_disks_serial_no[$i]) . "<br/>"; } //--> MEMORY $system_memory_qty = $_POST['system_memory_qty']; $system_memory_serial_no = $_POST['system_memory_serial_no']; $system_memory_manf = $_POST['system_memory_manf']; $system_memory_part_no = $_POST['system_memory_part_no']; $system_memory_size = $_POST['system_memory_size']; for ($i = 0; $i < count($system_memory_qty); $i++) { echo "qty " . clean($system_memory_qty[$i]) . "<br/>"; echo "serial " . clean($system_memory_serial_no[$i]) . "<br/>"; echo "manf " . clean($system_memory_manf[$i]) . "<br/>"; echo "part_no " . clean($system_memory_part_no[$i]) . "<br/>"; echo "size " . clean($system_memory_size[$i]) . "<br/>"; } This just outputs all the fields I post at the moment. Say I have 3 different disk types, it's going to loop 3 times. Is it possible to make this all into 1 giant query or am I better off doing each query separately? Each section is a different table. I have a search where I want to be able to search a string of words. The search is going to be looking in 2 different table joined by a left outer join. The tables are "quotes" and "categories". $sql="SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, c.quote_id, c.label FROM quotes q LEFT OUTER JOIN categories c ON q.id = c.quote_id WHERE ( q.username LIKE '$srch' OR q.quote LIKE '$srch' OR q.`by` LIKE '$srch' OR c.label LIKE '$srch')"; The above mysql statement works and returns values..BUT if say, I search "john" and "funny", and a quote is posted by the user "john", but has a category of "funny" it will output the same quote twice. I was wondering if there was a way to see if a quote has either 1 term or both terms, if so display that quote but only display it once. Below is what the query is outputting. [100] => Array ( [id] => 100 [username] => John [quote] => new test quote blah blah [by] => John [voteup] => 0 [votedown] => 0 [servtime] => 2010-12-02 @ 16:27:03 [label] => Array ( [0] => Historic [1] => Serious [2] => Funny ) ) Here is the code in full. //// $sword = explode(" ",$search); foreach($sword as $sterm){ $srch="%".$sterm."%"; echo"$srch<br />"; $sql="SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, c.quote_id, c.label FROM quotes q LEFT OUTER JOIN categories c ON q.id = c.quote_id WHERE ( q.username LIKE '$srch' OR q.quote LIKE '$srch' OR q.`by` LIKE '$srch' OR c.label LIKE '$srch')"; $result=mysql_query($sql); while($row=mysql_fetch_object($result)){ $quote[$row->id]['id'] = $row->id; $quote[$row->id]['username'] = $row->username; $quote[$row->id]['quote'] = $row->quote; $quote[$row->id]['by'] = $row->by; $quote[$row->id]['voteup'] = $row->voteup; $quote[$row->id]['votedown'] = $row->votedown; $quote[$row->id]['servtime'] = $row->servtime; $quote[$row->id]['label'][] = $row->label; } echo"<pre>"; print_r($quote); echo"</pre>"; I don't think this is the fastest way of doing this, as it loops for each item in the db, per each keyword that is search. Any help would be great!! -BaSk I am trying to export multiple csv files to download from 2 separate tables in my database. Some background: I have a web app that links to a phpmyadmin database. There are 2 tables in the database (entering and exiting). These tables hold the phone inventory information of employees currently entering or exiting the organization. The fields in my tables a location, firstname, lastname, username, extension, mac, type What I am trying to do is export the data in these 2 tables to CSV (which I have working now) but I need to have multiple CSVs for each. For example, for my exiting table, I need to have one CSV export all the fields in the table and I also need another CSV to export just the location and username field. I have a simple html form with a submit button which is currently working now: <form action="exportexiting.php" method="POST" style="width: 456px; height: 157px"> <div> <fieldset> <legend style="width: 102px; height: 25px"><strong>Entering CSV:</strong></legend> <input name="Export" type="submit" id="Export" value="Export"> </fieldset><br/> </div> </form> This links to my exportexiting.php file: <?php $host = 'localhost'; $user = 'root'; $pass = 'xxxx'; $db = 'PhoneInventory'; $table = 'exiting'; // Connect to the database $link = mysql_connect($host, $user, $pass); mysql_select_db($db); require 'exportcsv.inc.php'; exportMysqlToCsv($table); ?> Then to exportcsv.inc.php: <?php function exportMysqlToCsv($table,$filename = 'export.csv') { $csv_terminated = "\n"; $csv_separator = ","; $csv_enclosed = ''; $csv_escaped = "\\"; $sql_query = "select * from $table"; // Gets the data from the database $result = mysql_query($sql_query); $fields_cnt = mysql_num_fields($result); $schema_insert = ''; for ($i = 0; $i < $fields_cnt; $i++) { $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, stripslashes(mysql_field_name($result, $i))) . $csv_enclosed; $schema_insert .= $l; $schema_insert .= $csv_separator; } // end for $out = trim(substr($schema_insert, 0, -1)); $out .= $csv_terminated; // Format the data while ($row = mysql_fetch_array($result)) { $schema_insert = ''; for ($j = 0; $j < $fields_cnt; $j++) { if ($row[$j] == '0' || $row[$j] != '') { if ($csv_enclosed == '') { $schema_insert .= $row[$j]; } else { $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed; } } else { $schema_insert .= ''; } if ($j < $fields_cnt - 1) { $schema_insert .= $csv_separator; } } // end for $out .= $schema_insert; $out .= $csv_terminated; } // end while header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Length: " . strlen($out)); // Output to browser with appropriate mime type, you choose header("Content-type: text/x-csv"); //header("Content-type: text/csv"); //header("Content-type: application/csv"); header("Content-Disposition: attachment; filename=$filename"); echo $out; exit; } ?> Again, this is working perfectly for only exporting all the data from the exiting table into one CSV file. My question is, how can I make my one submit button export the 2 CSV files that I need? Thanks for the help... Hi, I've created an admin page that lists all piano sheet music that has been uploaded to my server. It displays the ID (autoincrement), Userid (user that uploaded sheet), Artist (name of the band) and Title (name of the song). On this page, I can either delete the row completely, or click on "Uploaded", which just sets it's uploaded column to 'yes'. Now I'm working on a button called "Uploaded All". This will set the uploaded column on all of the rows on the page to 'yes'. This way I don't have to click Uploaded for every single row. (view attached image if you are unsure what I am talking about) I'm not getting any errors when clicking "Uploaded All". When clicking it, it doesn't update the table rows in the database as it should. Take a look at my code: Tip: The area of the code that I'm currently having troubles with is here $usersids[$i] = $row['id']; $i++; } echo " <tr> <td align='center' width='10' colspan='6'><a href='uploadedsheets.php?confirm=all'>Uploaded All</a></td> </tr>"; if ($confirm=="all") { $i = 0; while($row = mysql_fetch_array($result)) { mysql_query('UPDATE `upload` SET `uploaded`="yes" WHERE id = ' . $usersids[$i]); $i++; } echo "<SCRIPT language='JavaScript'><!-- window.location='uploadedsheets.php';//--> </SCRIPT>"; } Inside the above code after the if statement, I tried echoing out $usersids, and it showed correctly. So the problem must be in the update query or the array. Full Code: <?php session_start(); include_once('../inc/connect.php'); include_once('../inc/admin.php'); if(!isset($_SESSION['sort_counter'])) {$_SESSION['sort_counter'] = 1;} if(($_SESSION['sort_counter']%2) == 0){ //test even value $sortcount = "DESC"; }else{ //odd value $sortcount = ""; } $result = mysql_query("SELECT * FROM upload WHERE uploaded='no' ORDER BY id"); $unconfirmedquery = mysql_query("SELECT uploaded FROM upload WHERE uploaded='no'"); $unconfirmedcount = mysql_num_rows($unconfirmedquery); $confirmedquery = mysql_query("SELECT uploaded FROM upload WHERE uploaded='yes'"); $confirmedcount = mysql_num_rows($confirmedquery); $sort = $_GET['sort']; $delete = $_GET['delete']; $confirm = $_GET['confirm']; ///////////////////////////////// if ($sort=='id'){ // $result = mysql_query("SELECT * FROM users ORDER BY id"); $result = mysql_query("SELECT * FROM upload WHERE uploaded='no' ORDER BY id $sortcount"); $_SESSION['sort_counter'] = $_SESSION['sort_counter'] + 1; //increment after every run } if ($sort=='userid'){ // $result = mysql_query("SELECT * FROM users ORDER BY username"); $result = mysql_query("SELECT * FROM upload WHERE uploaded='no' ORDER BY userid $sortcount"); $_SESSION['sort_counter'] = $_SESSION['sort_counter'] + 1; //increment after every run } if ($sort=='artist'){ // $result = mysql_query("SELECT * FROM users ORDER BY email"); $result = mysql_query("SELECT * FROM upload WHERE uploaded='no' ORDER BY artist $sortcount"); $_SESSION['sort_counter'] = $_SESSION['sort_counter'] + 1; //increment after every run } if ($sort=='title'){ // $result = mysql_query("SELECT * FROM users ORDER BY email"); $result = mysql_query("SELECT * FROM upload WHERE uploaded='no' ORDER BY title $sortcount"); $_SESSION['sort_counter'] = $_SESSION['sort_counter'] + 1; //increment after every run } if ($sort=='file'){ // $result = mysql_query("SELECT * FROM users ORDER BY email"); $result = mysql_query("SELECT * FROM upload WHERE uploaded='no' ORDER BY file $sortcount"); $_SESSION['sort_counter'] = $_SESSION['sort_counter'] + 1; //increment after every run } /// FIX THIS AREA if ($confirm=="true" && isset($_GET['id'])) { mysql_query('UPDATE `upload` SET `uploaded`="yes" WHERE id = ' . (int)$_GET['id']); echo "<SCRIPT language='JavaScript'><!-- window.location='uploadedsheets.php';//--> </SCRIPT>"; } if ($delete=="true" && isset($_GET['id'])) { mysql_query('DELETE FROM `upload` WHERE id = ' . (int)$_GET['id']); echo "<SCRIPT language='JavaScript'><!-- window.location='uploadedsheets.php';//--> </SCRIPT>"; } if ($delete=="false" && isset($_GET['id'])) { echo "<SCRIPT language='JavaScript'><!-- window.location='uploadedsheets.php';//--> </SCRIPT>"; } ///////////////////////////////// echo " <html> <head> <title>Uploaded Sheets</title> <style> a:link{ text-decoration: none; color: #519904; } a:visited{ text-decoration: none; color: #519904; } a:hover{ text-decoration: none; color: #4296ce; } #headcont{ width: 900px; height: 20px; margin-left: auto; margin-right: auto; } #unconfirmed{ width: 450px; text-align: center; float: left; background-color: #cccccc; } #confirmed{ width: 450px; text-align: center; float: left; background-color: #72A4D2; } </style> </head> <body> "; include_once('../inc/navadmin.php'); echo "<div style='font-size: 28px; text-align: center;'>Uploaded Sheets</div> <div id='headcont'> <div id='unconfirmed'>Not Uploaded Sheets: ".$unconfirmedcount."</div> <div id='confirmed'>Uploaded Sheets: ".$confirmedcount."</div> </div><br /> <table border='1' align='center'> <tr> <th bgcolor='#cccccc'><a href='uploadedsheets.php?sort=id'>ID</a></th> <th bgcolor='#cccccc'><a href='uploadedsheets.php?sort=userid'>UserID</a></th> <th bgcolor='#cccccc'><a href='uploadedsheets.php?sort=artist'>Artist</a></th> <th bgcolor='#cccccc'><a href='uploadedsheets.php?sort=title'>Title</a></th> <th bgcolor='#cccccc'><a href='uploadedsheets.php'>Uploaded</a></th> <th bgcolor='#cccccc'><a href='uploadedsheets.php'>Delete</a></th> </tr>"; echo "<script type='text/javascript'> function show_delete() { var r=confirm('Delete?'); if (r==true) { // Delete return true; } else { // Don't Delete return false; } } "; echo " function show_undelete() { var r=confirm('Undelete?'); if (r==true) { // Undelete return true; } else { // Don't Undelete return false; } } </script>"; $usersids = ""; $i = 0; while($row = mysql_fetch_array($result)) { // $active = $row['active']; $color = "#ffffff"; $deleted = "Delete"; if ($active=='no'){ $color = "#f43636"; $deleted = "Undelete"; $active = "false"; $alert = "show_undelete"; } else{ $active = "true"; $alert = "show_delete"; } // echo "<tr>"; echo "<td align='center' width='40' bgcolor='$color'>" .$row['id']. "</td>"; echo "<td align='center' width='40'>" .$row['userid']. "</td>"; echo "<td align='center' width='230'>".ucwords($row['artist'])."</td>"; echo "<td align='center' width='230'>".ucwords($row['title'])."</td>"; echo "<td align='center' width='10'><a href='uploadedsheets.php?confirm=true&id=" .$row['id'] . "'>Uploaded</a></td>"; echo "<td align='center' width='10'><a href='uploadedsheets.php?delete=$active&id=" .$row['id']. "' onclick='return $alert()'>$deleted</a></td>"; echo "</tr>"; $usersids[$i] = $row['id']; $i++; } echo " $usersids[0] <tr> <td align='center' width='10' colspan='6'><a href='uploadedsheets.php?confirm=all'>Uploaded All</a></td> </tr>"; if ($confirm=="all") { $i = 0; while($row = mysql_fetch_array($result)) { mysql_query('UPDATE `upload` SET `uploaded`="yes" WHERE id = ' . $usersids[$i]); $i++; } echo "<SCRIPT language='JavaScript'><!-- window.location='uploadedsheets.php';//--> </SCRIPT>"; } echo "</table>"; // Footer echo " </body> </html> "; ?> hello guys moving on from inserting multiple records im now stuck with updating multiple records. i can get most of the details nailed but i need it to match the id's for the table the code i have in the sql part are Code: [Select] if (isset($_POST['submit'])) { //Assign each array to a variable $StaffMember = $_POST['StaffMember']; $referrer = $_POST['referrer']; $referred = $_POST['referred']; $SentOut = $_POST['SentOut']; $today = date("y.m.d H:i:s"); $user_id = $_SESSION['user_id']; $IssueNum = $_POST['Referrerid']; $limit = count($StaffMember); $values = array(); // initialize an empty array to hold the values for($k=0;$k<$limit;$k++){ $msg[] = "$limit New KPI's Added"; $values[$k] = "( '{$StaffMember[$k]}', '{$referrer[$k]}', '{$referred[$k]}', '{$SentOut[$k]}', '{$today}', '{$user_id}' )"; // build the array of values for the query string } $query = "UPDATE `Referrer` (StaffMember, referer, referred, SentOut, SentOutDate, SentOutBy) VALUES " . implode( ', ', $values ) . " WHERE IssueNum= '{$IssueNum[$k]}'"; echo $query; } i obviously want the records updating where the issuenumber is the same and if the check box in the form is ticked help with this one? I am trying to update multiple rows in a mysql db through php but I am getting an error message in this line: $size = count($_POST['uid']); My db field names are uid (which autoincrements and does not need to be updated), fname, lname and email fields. Here is a link for the project I need to develophttp://users.cis.fiu.edu/~vagelis/classes/COP5725/project.htm Here is the content for the first page: Code: [Select] <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head> <style type="text/css"> body { margin:50px 0px; padding:0px; text-align:center; font:13px Tahoma,Geneva,sans-serif } #content { width:1000px; margin:0px auto; text-align:left; padding:15px; border:1px dashed #333; background-color:#eee; } </style> <link rel="icon" href="http://www.mysite.com/favicon.ico" type="image/vnd.microsoft.icon" /> </head> <body> <div id='content'><h3><center>Update Information</center></h3> <?php //create a connection $connection = mysql_connect("localhost","root","r2d2c3po"); //test the connection if (!$connection) { die("Database connection failed: " . mysql_error()); } //select database to use mysql_select_db("music_social_network", $connection); //setup query $sql = "SELECT * FROM `user` ORDER BY uid"; //get results from query or die $result = mysql_query($sql, $connection) or die(mysql_error()); //fetch rows from query $rows = mysql_fetch_assoc($result); ?> <?php // find out how many records there are to update $size = count($_POST['uid']); // start a counter in order to number the input fields for each record $i = 0; print "<table width='100%' border='0' cellspacing='1' cellpadding='0'><tr><td>"; // open a form print "<form name='namestoupdate' method='post' action='update.php'> <table width='100%' border='0' cellspacing='1' cellpadding='1'><tr> <td align='center'><strong>User ID</strong></td> <td align='center'><strong>First Name</strong></td> <td align='center'><strong>Last Name</strong></td> <td align='center'><strong>Email</strong></td> </tr>\n"; // start a loop to print all of the users with their information // the mysql_fetch_array function puts each record into an array. each time it is called, it moves the array counter up until there are no more records left while ($Update = mysql_fetch_array($result)) { print "</tr>\n"; // assuming you have three important columns (the index (id), the course name (course), and the book info (bookinfo)) // start displaying the info; the most important part is to make the name an array (notice bookinfo[$i]) print "<td align='center'><p>{$Update['uid']}</p></td>\n"; print "<td align='center'><input type='text' fname='fname[$i]' value='{$Update['fname']}' /></td>"; print "<td align='center'><input type='text' size='40' lname='lname[$i]' value='{$Update['lname']}' /></td>\n"; print "<td align='center'><input type='text' size='40' email='email[$i]' value='{$Update['email']}' /></td>\n"; print "</tr>\n"; // add 1 to the count, close the loop, close the form, and the mysql connection ++$i; } print "<tr> <td colspan='4' align='center'><input type='submit' value='submit' />"; print "</td> </tr> </table> </td> </tr> </form> </table>"; mysql_close($connection); ?><br /><br /><div></body> </html> here is the content for the update.php page: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head> <style type="text/css"> body { margin:50px 0px; padding:0px; text-align:center; font:13px Tahoma,Geneva,sans-serif } #content { width:1000px; margin:0px auto; text-align:left; padding:15px; border:1px dashed #333; background-color:#eee; } </style> <link rel="icon" href="http://www.mysite.com/favicon.ico" type="image/vnd.microsoft.icon" /> </head> <body> <div id='content'><h3><center>Success! </center></h3> <table width='100%' border='0' cellspacing='1' cellpadding='0'><tr><td> <table width='100%' border='0' cellspacing='1' cellpadding='1'> <tr> <td align='center'><strong>User ID</strong></td> <td align='center'><strong>First Name</strong></td> <td align='center'><strong>Last Name</strong></td> <td align='center'><strong>Email</strong></td> </tr> <?php //create a connection $connection = mysql_connect("localhost","root","r2d2c3po"); //test the connection if (!$connection) { die("Database connection failed: " . mysql_error()); } //select database to use mysql_select_db("music_social_network", $connection); //setup query $sql = "SELECT * FROM `user` ORDER BY uid"; //get results from query or die $result = mysql_query($sql, $connection) or die(mysql_error()); //fetch rows from query $rows = mysql_fetch_assoc($result); // find out how many records there are to update $size = count($_POST['uid']); // start a loop in order to update each record $i = 0; while ($i < $size) { // define each variable $uid = $_POST['uid'][$i]; $fname = $_POST['fname'][$i]; $lname = $_POST['lname'][$i]; $email = $_POST['email'][$i]; // do the update and print out some info just to provide some visual feedback $query = "UPDATE `user` SET `fname` = '$fname', `lname` = '$lname', `email` = '$email' WHERE `uid` = '$uid' "; mysql_query($sql) or die ("Error in query: $query"); print " </tr> <td align='left'><p>$uid</p></td> <td align='left'>$fname</td> <td align='left'>$fname</td> <td align='left'>$lname</td> </tr> "; ++$i; } mysql_close($connection); ?> <tr> <td colspan='4' align='center'> </td> </tr> </table> </td> </tr> </table></div></body></html> Mod edit: [code] . . . [/code] tags added. I know how to pull data from sql table and limit down the results, what i wanted to do is pull data from a sql table 'finished' and list data that has paid='0' this is then limited to show the amount of rows set by variable 'maxPay' here is my list what im pulling atm; <h2>Payment </h2> <?php if($userType[user_type]==28){ $queryPay = mysql_query("SELECT * FROM finished WHERE paid='0'&&method!='FALSE'&&method!='credit';") or die(mysql_error()); $queryAdmin = mysql_query("SELECT maxPay FROM `admin`;") or die(mysql_error()); $row = mysql_num_rows($queryPay); //echo $row."row<br />"; $rowsArray = mysql_fetch_assoc($queryAdmin); $rows = $rowsArray[maxPay]; //echo $rows."rows<br />"; if($rows>$row){ $r=$row; } else{ $r=$rows; } $s=0; while($s<$r){ if($_POST[complete]==TRUE){ mysql_query("UPDATE `finished` SET `paid`='$_POST[paid]' WHERE `auctionID`='$_POST[aid]';"); $_POST[complete] = FALSE; } $s++; } $t=0; while($t<$r){ $queryuser = mysql_query("SELECT * FROM finished WHERE paid='0'&&method!='FALSE'&&method!='credit';") or die(mysql_error()); $user = mysql_result($queryuser, $t, "username"); //echo $user; $item = mysql_result($queryuser, $t, "itemName"); //echo $item; $value = mysql_result($queryuser, $t, "marketPrice"); //echo $value; $method = mysql_result($queryuser, $t, "method"); //echo $method; $id = mysql_result($queryuser, $t, "auctionID"); //echo $id; //echo $method; if($method=='isk'){ ?> <FORM method="post" action="pay.php"> <input type="hidden" name=complete value="TRUE" /> <input type="hidden" name=paid value="YES" /> <input type="hidden" name=username value="<? echo $user; ?>" /> <input type="hidden" name=aid value="<? echo $id; ?>" /> <input type="submit" value="Paid" /><? echo $user." won a ".$item." he selected to receive ".$value."ISK."; echo "<br />"; ?></FORM><? } elseif($method=='ship'){ ?> <FORM method="post" action="pay.php"> <input type="hidden" name=complete value="TRUE" /> <input type="hidden" name=paid value="YES" /> <input type="hidden" name=username value="<? echo $user; ?>" /> <input type="hidden" name=aid value="<? echo $id; ?>" /> <input type="submit" value="Paid" /><? echo $user." won a ".$item." he selected to receive the ship."; echo "<br />"; ?></FORM><? } else{ } $t++; } } else{ echo "You are not an Admin."; } ?> and pay.php: <?php header('Location: /admin.php'); include "connect.php"; echo $_POST[aid]; if($_POST[complete]==TRUE){ $tempID = (($_POST[aid])+1); mysql_query("UPDATE `finished` SET `paid`='$_POST[paid]' WHERE `auctionID`='$tempID';") or die(mysql_error()); $_POST[complete] = FALSE; } ?> at the moment i am listing all my data, and processing it using pay.php, each dataset has a "paid" button, i want to change this to using a checkbox at end of each data row, and a submit button at the bottom. this way i can select which rows to update and tick the checkbox, then click submit and those rows will then be updated the value '0' in db to a value of 'YES'. hope i explained myself clearly enough for some help. im a relative php noob still in training. This is the form that I'm using and it populates just fine but when you make the changes I can't figure out how to get it to update each record in my database. Code: [Select] <table width="100%" cellspacing="1" cellpadding="2" border="0"> <form action="<?php echo $_SERVER["PHP_SELF"] . "?update=1"; ?>" method="post"> <tr> <td><b>Category Name</b></td> <td><b>Category Description</b></td> <td><b>Order</b></td> <td align="right"><input type="submit" value="Update"></td> </tr> <?php read_cat_list($cat); for ($i = 0; $i < $cat["count"]; $i++) { echo "<tr>\n"; echo "<td width=\"30%\" valign=\"top\"><input type=\"text\" name=\"cat_name_" . safe_string($cat[$i]["cat_id"]) . "\" size=\"30\" maxlength=\"250\" value=\"" . safe_string($cat[$i]["cat_name"]) . "\"></td>\n"; echo "<td width=\"36%\" valign=\"top\"><textarea name=\"cat_desc_" . safe_string($cat[$i]["cat_id"]) . "\" rows=\"2\" cols=\"30\">" . safe_string($cat[$i]["cat_desc"]) . "</textarea></td>\n"; echo "<td width=\"10%\" valign=\"top\"><input type=\"text\" name=\"cat_order_" . safe_string($cat[$i]["cat_id"]) . "\" size=\"3\" maxlength=\"3\" value=\"" . safe_string($cat[$i]["cat_order"]) . "\"></td>\n"; echo "<td width=\"24%\" valign=\"top\">[ <a href=\"#\" onmouseover=\"window.status = 'Delete " . safe_string($cat[$i]["cat_name"]) . "'; return true;\" onmouseout=\"window.status = ''; return true;\" onclick=\"javascript:del_cat(" . $cat[$i]["cat_id"] . ", '" . safe_string($cat[$i]["cat_name"]) . "'); return false;\">Delete</a> ]</td>\n"; echo "</tr>\n"; } ?> <tr> <td colspan="4" align="right"><input type="submit" value="Update"></td> </tr> </form> </table> the safe_string function just cleans up the output/input from the database, This next block is my form processor for this form. Code: [Select] <?php function update_cats($vars) { $err = ""; #if ($SESSION["level"] != ADMIN) { # $err = ERR_NOT_ENOUGH_ACCESS; #} else { $temp = array_keys($vars); for ($i = 0; $i < count($temp); $i++) { if (substr($temp[$i], 0, 9) == "cat_name_") { if ($vars[$temp[$i]] == "") { $err = "Category names cannot be blank."; break; } else { $name_query["cat_name"] .= substr($temp[$i], 9) . ", "; } } } for ($i = 0; $i < count($temp); $i++) { if (substr($temp[$i], 0, 9) == "cat_desc_") { $desc_query["cat_desc"] .= substr($temp[$i], 9) . ", "; } } for ($i = 0; $i < count($temp); $i++) { if (substr($temp[$i], 0, 10) == "cat_order_") { if ($vars[$temp[$i]] == "") { $err = "Category orders cannot be blank."; break; } else { $order_query["cat_order"] .= substr($temp[$i], 10) . ", "; } } } #} if (!$err) { if ($name_query["cat_name"]) { $update_name_query = "update category"; $update_name_query .= " set cat_name = '" . $name_query["cat_name"] . "'"; $update_name_query .= " where cat_id in (" . substr($name_query["cat_name"], 0, -2) . ")"; update_db($update_name_query); } if ($desc_query["cat_desc"]) { $update_desc_query = "update category"; $update_desc_query .= " set cat_desc = '" . $name_query["cat_desc"] . "'"; $update_desc_query .= " where cat_id in (" . substr($desc_query["cat_desc"], 0, -2) . ")"; update_db($update_desc_query); } if ($order_query["cat_order"]) { $update_order_query = "update category"; $update_order_query .= " set cat_order = '" . $name_query["cat_order"] . "'"; $update_order_query .= " where cat_id in (" . substr($order_query["cat_order"], 0, -2) . ")"; update_db($update_order_query); } } return $err; } ?> update_db is my database caller, if you need any of the functions that I use that are not here please post back and tell me. now when I process the form all my fields change to this: cat_name: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, cat_desc: cat_id: 0 for all the records in the database table, I'm just trying to update each record with the values it needs Here is my database table with the data. Code: [Select] CREATE TABLE `category` ( `cat_id` int(11) NOT NULL AUTO_INCREMENT, `cat_name` varchar(255) NOT NULL, `cat_desc` text NOT NULL, `cat_order` int(11) NOT NULL, PRIMARY KEY (`cat_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ; -- -- Dumping data for table `category` -- INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(1, 'Hand Tossed Pizza', '', 1); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(2, 'Hand Tossed Specialty Pizzas', '', 2); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(3, 'Chicago Style Deep Dish', '', 3); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(4, 'Specialty Chicago Style Deep Dish', '', 4); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(5, 'Chicken Wings & Tenderloins', '', 5); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(6, 'Hot Sides', '', 6); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(7, 'Hot Sandwiches', '', 7); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(8, 'Cold Sandwiches', '', 8); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(9, 'Pastas', '', 9); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(10, 'Fresh Salads', '', 10); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(11, 'Fresh Breads', '', 11); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(12, 'Soups', '', 12); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(13, 'Kids Menu', '', 13); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(14, 'Drinks', '', 14); INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(15, 'Desserts', '', 15); Hope I have provided plenty of info on what I got and what I need for it todo Thanks Hi all, I'm currently working on an admin page and I'm trying to figure out how I can make an update query where all information will be updated. The code below is where I extract all the Users and their status from the database and now I when the statuses are updated in the text fields they need to be send to the database. Does anyone know how this could be done? Thnx Ryflex <?php require_once('auth.php'); require_once('config.php'); $global_dbh = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die("Could not connect to database"); mysql_select_db(DB_DATABASE, $global_dbh) or die("Could not select database"); $user_query = "SELECT * FROM members"; $user_result = mysql_query($user_query); ?> <form ID="gotoresource" NAME="gotoresource" METHOD="POST" ACTION="admin_exec.php"> <table width="500" border="1" align="center" cellpadding="2" cellspacing="0"> <tr> <td><b>ID</b></td> <td><b>User</b></td> <td><b>Status</b></td> </tr> <?php while($row = mysql_fetch_assoc($user_result)) { echo "<tr>"; echo "<td>"; echo $row['member_id']; echo "</td>"; echo "<td>"; echo $row['login']; echo "</td>"; ?> <td> <input type="text" name="status" value="<?php echo $row['status'];?>" /> </td> <?php echo "</tr>"; } ?> <input type="image" src="/images/button.gif" alt="Submit button"> </form> <html> <head> <title>AdminPage</title> <link href="loginmodule.css" rel="stylesheet" type="text/css" /> </head> <body> </body> </html> Hi, I have a row called code in mysql data base now what I want to do is something like this $rand=rand(1111,9999); $code=mysql_query("UPDATE member SET code='$rand' WHERE clan_id='1'"); At the moment its updating the code row with the same random number. What I want to achieve is a unique number for each member. For instance Currently if the $rand=1289 lets suppose Every code row will become 1289 I want the code rows to be different to each other. I tried a few things up my sleeve but no luck. How can i do this? thanks any help much appreciated. I have problem that only last text field is updated, how should I fix this? Here's the code <?php if(isset($_POST["update"])){ mysql_query("UPDATE categories SET name_category = '".$_POST['category']."' WHERE ID= ".$_POST['currentCat']." ") or die(mysql_error()); mysql_query("UPDATE podkategorije SET name_subcategory = '".$_POST['subcategory']."' WHERE id_subCat= ".$_POST['currentSubCat']." ") or die(mysql_error()); } ?> <form action="" method="post" > <?php //creating texfields from db $query = "SELECT k.ID, k.name_category, pk.name_subcategory, pk.id_subCat FROM `categories` AS k JOIN `subcategories` AS pk ON pk.id_mainCat = k.ID"; $result = mysql_query($query) or die(mysql_error()); $currentCat = false; while($row = mysql_fetch_array($result)) { //so it doesn't repeat itself if($currentCat != $row['ID']) { //display of main Categories ?> <ul> <li> <br/><input name="categories" type="text" value="<?php echo $row['name_category']; ?>" /> </li> </ul> <? $currentCat = $row['ID']; } //display subcategories ?> <input name="subcategories" type="text" value="<?php echo $row['name_category']; ?>" /><br/> <input type="hidden" name="currentCat" value="<?php echo $row['ID']; ?>" /> <input type="hidden" name="currentSubCat" value="<?php echo $row['id_subCat']; ?>" /> <? } ?> <br /> <input type="button" value="Back" onClick="history.go(-1);return true;"> <input type="submit" value="Update" name="update"/> </form> |
|||||||