|
PHP - In An Update Operation, Need Help To Set Default Value Of A "lookup Dropdown" Based On Value In Table
Hello all,
For the UPDATE portion of my CRUD WebApp what I would like to do is to bring in (and display) the values (of a selected row) from my transaction table.
This is working just fine for all fields which are of the "input type". The problem I'm having is with two fields which are of the "select type" i.e. dropdown listboxes.
For those two fields, I would like to bring in all the valid choices from the respective lookup/master tables, but then have the default/selected value be shown based on what's in the transaction table. The way I have it right now, those two fields are showing (and updating the record with) the very first entry's in the two lookup tables/select query.
The attached picture might make things a little bit clearer. You'll notice in the top screenshot that the first row (which is the one I'm selecting to update) has a "Store Name" = "Super Store" and an "Item Description" = "Old Mill Bagels". Now, when I click the "update" botton and I'm taken to the update screen, the values for those two fields default to the very first entries in the SELECT resultset i.e. "Food Basics" and "BD Cheese Strings". Cricled in green (to the top-left of that screenshot) is the result of an echo that I performed, based on the values that are in the transaction record.
I cannot (for the life of me) figure out how to get those values to be used as default/selected values for the two dropdown's...so that if a user does not touch those two dropdown fields, the values in the transaction table will not be changed.
Your help will be greatly appreciated.
Here's a portion of the FORM code:
<form class="form-horizontal" action="update.php?idnumber=<?php echo $idnumber?>" method="post">
<?php
// Connect to Store_Name (sn) table to get values for dropdown
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT DISTINCT store_name FROM store_master ORDER BY store_name ASC";
$q_sn = $pdo->prepare($sql);
$q_sn->execute();
$count_sn = $q_sn->rowCount();
$result_sn = $q_sn->fetchAll();
Database::disconnect();
// Connect to Item_Description (id) table to get values for dropdown
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT DISTINCT product_name FROM product_master ORDER BY product_name ASC";
$q_id = $pdo->prepare($sql);
$q_id->execute();
$count_id = $q_id->rowCount();
$result_id = $q_id->fetchAll();
Database::disconnect();
foreach($fields AS $field => $attr){
$current_store_name = $values['store_name'];
$current_item_description = $values['item_description'];
//Print the form element for the field.
if ($field == 'store_name')
{
echo $current_store_name;
echo '<br />';
echo $current_item_description;
echo '<div class="control-group">';
echo "<label class='control-label'>{$attr['label']}: </label>";
echo '<div class="controls">';
//Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
echo '<select class="store-name" name="store_name">';
// echo '<option value="">Please select...</option>';
foreach($result_sn as $row)
{
echo "<option value='" . $row['store_name'] . "'>{$row['store_name']}</option>";
}
// $row['store_name'] = $current_store_name;
echo "</select>";
echo '</div>';
echo '</div>';
}
elseif ($field == 'item_description')
{
echo '<div class="control-group">';
echo "<label class='control-label'>{$attr['label']}: </label>";
echo '<div class="controls">';
echo '<select class="item-desc" name="item_description">';
// echo '<option value="">Please select...</option>';
foreach($result_id as $row)
{
echo "<option alue='" . $row['product_name'] . "'>{$row['product_name']}</option>";
}
echo "</select>";
echo '</div>';
echo '</div>';
}
else
{
echo '<div class="control-group">';
echo "<label class='control-label'>{$attr['label']}: </label>";
echo '<div class="controls">';
//Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
echo '<input type="text" name="'.$field.'"' . (isset($values[$field]) ? ' value="'.$values[$field].'"' : '') . ' /></label>';
echo '</div>';
echo '</div>';
}
And here's some other declarations/code which I think might be required.
$fields = array(
'store_name' => array('label' => 'Store Name',
'error' => 'Please enter a store name'),
'item_description' => array('label' => 'Item Description',
'error' => 'Please enter an item description'),
'qty_pkg' => array('label' => 'Qty / Pkg',
'error' => 'Please indicate whether it\'s Qty or Pkg'),
'pkg_of' => array('label' => 'Pkg. Of',
'error' => 'Please enter the quantity'),
'price' => array('label' => 'Price',
'error' => 'Please enter the price'),
'flyer_page' => array('label' => 'Flyer Page #',
'error' => 'Please enter the flyer page #'),
'limited_time_sale' => array('label' => 'Limited Time Sale',
'error' => 'Please enter the days for limited-time-sale'),
'nos_to_purchase' => array('label' => 'No(s) to Purchase',
'error' => 'Please enter the No. of items to purchase')
);
...
...
....
{
// If [submit] isn't clicked - and therfore POST array is empty - perform a SELECT query to bring in
// existing values from the table and display, to allow for changes to be made
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM shoplist where idnumber = ?";
$q = $pdo->prepare($sql);
$q->execute(array($idnumber));
$values = $q->fetch(PDO::FETCH_ASSOC);
if(!$values)
{
$error = 'Invalid ID provided';
}
Database::disconnect();
}
If there's anything I've missed please ask and I'll provide.
Thanks
Similar TutorialsHi all, firstly apologies as this is a cross post from another forum and we have hit a block.. I am hoping that opening this up to another set of gurus we can get a resolution. What I am trying to achieve is this... I have 2 tables Main and FinancialYear. Main holds all data which I use a form to post the data to it..(all works fine). I use this code to create a drop down in the insert.php form. again this works. Code: [Select] <tr><td>Financial Year: xxxx/xxxx</td><td> <!-- pulls the data from the table variable to populate the dropdown menu --> <?php $database = 'Projects_Main'; $fintable = 'FinancialYear'; if (!mysql_connect($db_host, $db_user, $db_pwd)) die("Can't connect to database 'cos somethin' is wrong"); if (!mysql_select_db($database)) die("Can't select database"); $result = mysql_query("SELECT FinancialYear_id, FinancialYear FROM {$fintable} order by FinancialYear"); $options=""; while ($row=mysql_fetch_array($result)) { //$id=$row["FinancialYear_id"]; $thing=$row["FinancialYear"]; $options.="<OPTION VALUE=\"$thing\">".$thing.'</option>'; } ?> <SELECT NAME="FinancialYear"> <OPTION VALUE=0>Choose</OPTION> <?=$options?> </SELECT> </td></tr> What I have done is built another form which list all records in the database and creates an update url for every record that passes the field Project_id where i use $_get to retrieve the Project_id to retrieve the relevant data into the update.php form. I am able to populate the form with all the correct information BUT I am looking to introduce some dropdowns to aid updating the data and provide consistency to the data. . Code: [Select] // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db('Projects_Main')or die("cannot select DB"); // get value of id that sent from address bar $Project_id=$_GET['Project_id']; //define vars $FinancialYear=$_POST['FinancialYear']; // other vars defined here also.. about 30 // Retrieve data from database $sql="SELECT * FROM Main WHERE Project_id='$Project_id'"; $result=mysql_query($sql); $rows=mysql_fetch_array($result); ?> //update_record_ac.php posts the data to the dbase. <form name="form1" method="post" action="update_record_ac.php"> <center> <table> <tr><td><b>Store Details<b></td></tr> <tr><td>Financial Year:</td><td> // takes the data from $rows and present to form <input name="FinancialYear" type="text" id="FinancialYear" value="<?php echo $rows['FinancialYear']; ?>"> // this is where I need to create the drop down.. see my other comments in the post..... </td></tr> the financialYear table consists if the following; financialyear_id - pri, auto inc. ---- data format is 2010/2011, 2011/2012.... financialyear the main table contains 30 fields .. won't list em all... Project_id - pri, auto inc financialyear I need the drop down to pull the data from the financialyear table and then to present or focus on the currently stored data... so if the store value in the table Main is 2010/2011 if Ii was to select the update url in the list_record.php it will pull all the relevant data into update_record.php form. the financialyear field in the form should be a dropdown with all the financial years listed but the 2010/2011 is selected or focused. I still need to be able to change the entry and post this back to the table Main..... So the dropdown contains the list of years from the financialyear table but when the record is pulled from table main the year that is stored in table Main should be highlighted in the dropdown and I should be able to select a new record and post back to the table Main.. any thoughts... please don't slate for the cross post, I haven't sanatised the data at any stage. I know i'm open to injection attacks. and yes my code is a little dirty... all these will be rectified as i finalise the process and ensure the consept works. Thanks for taking the time to read and hopefully you are able to understand the requirement and are able to assist. thanks Balgrath I have found postings close, but not close enough to find my error. I am looking up data from a MySql table and putting it in a dropdown box on a form. I can select the item, but apparently not really. I am not able to echo it, or post it to a record. I'm sure I am missing something simple, but... Code attached if anyone can show me the errors of my ways. Thank you. Hi I have been trying to get a value to be selected in a mysql populated dropdown list but can't get it to work and was hoping someone could help I have a database with user info in it and this is an update page where they can update their details. The code i have (which doesn't work) is: <select name="agency"> <? $query1 = mysql_query("SELECT * FROM agents ORDER BY agent ASC",$connect); while($myrow = mysql_fetch_assoc($query1)){ $agent = $myrow['agent']; echo "<option"; if ($agent == $agency) { echo "selected='selected'"; } echo ">$agent</option>"; } ?> </select> The $agency value is the current agency which is stored in the users profile and the value does exist in the list which is being populated (also, i have define $agency further up in my code) so i don't know why the selected value won't display. No value is displayed in the dropdown list on the page - but the values are in the list if i remove the selected='selected' part of the code. Any help yould be greatly appreciated. Merry Christmas Andy Hi guys, Before we go further, yes, I know I should use prepared statements. This is just a project that will probably never go live. If I do decide to go live, I will change to prepared statements. Anyways, I am populating a text box from the selection of an options dropdown and updating MySQL. This all works fine. However, what I want to do is have the newly inserted option display by default in the dropdown. Hope this makes sense. Here is the dropdown code with the select statement...
<div class="row form-group">
<div class="col-6">
<div class="form-group"><label for="location" class=" form-control-label">location</label>
<?php
$sql = "SELECT location_name, location_phone FROM locations";
$result = $con->query($sql);
?>
<select name="location" id="location" onchange="myFunction()" class="form-control">
<?php
while($r = mysqli_fetch_row($result))
{
echo "<option data-location_name='$r[1]' data-location_phone='$r[2]' value='$r[0]'> $r[0] </option>";
}
?>
</select>
<label>Phone</label><input type="text" class="form-control" name="location_phone" id="location_name" value = "<?php echo $row['location_phone']; ?>"/>
I am grabbing the location name and phone number from locations. Then inserting them into tours. So after the form is submitted, I want the location name to stay in the dropdown. This is in the table "tours". How do I implement that in here? Thanks heaps. Can anyone post a generic update function to update mysql table. The manual approach: update $tablename set $column1='a', $column2='b' where $id=$value; Hey guys, I am wanting to select a dropdown value based on the value of 'level' in the row of the user select by a $_GET. It will house the ranks of the user. Here is my script. RANK <?php mysql_connect("localhost","root","") or die(mysql_error()); mysql_select_db("chat"); $result = mysql_query("SELECT * FROM users WHERE user_id = '$_GET[id]'"); $row = mysql_num_rows($result); ?> <form id="main_form" name="main_form" method="post" action=""> <select name="rank"> <option value="0" <?php if($row['level']=="0") { echo "selected"; }?>>Unactivated</option> <option value="1" <?php if($row['level']=="1") { echo "selected"; }?>>Banned</option> <option value="2" <?php if($row['level']=="2") { echo "selected"; }?>>Regular User</option> <option value="3" <?php if($row['level']=="3") { echo "selected"; }?>>Donator</option> <option value="4" <?php if($row['level']=="4") { echo "selected"; }?>>Moderator</option> <option value="5" <?php if($row['level']=="5") { echo "selected"; }?>>Administrator</option> <option value="6" <?php if($row['level']=="6") { echo "selected"; }?>>Owner</option> </select> <input type="submit" id="main_submit" name="main_submit" value="submit" /> </form> It is not selecting for some reason at all. Can someone tell me what I am doing wrong? Hi all I am currently in the process of scoping out a script and I am needing some help. I have a mysql table, that has several rows, each with the following: title | description | price | rss_url | delivery_cost | retailer_message | discount | total --------------------------------------------------------------------------------------------------------------- Halo: Reach (XBOX 360) | Description text | 35.99 | http://www.easycontentunits.com/rss/54626/669/rss2.rss | On sale at Gameplay | 0 | 35.99 ---------------------------------------------------------------------------------------------------------------------------------------------- Halo: Reach | Description text | 36.89 | http://www.easycontentunits.com/rss/54626/669/rss2.rss | On sale at Toys R Us | 0 | 36.89 What I need to do is the following: Select each row and extract the rss_url Parse the rss_url and extract the above details from the rss feed If the details in the rss feed has changed, update the row If the actual rss feed item is no longer in the rss feed, delete it from the table. So If the rss feed changes the first rows details in my db has the price field updated to 40.00, I need to update the row and the price field will change from 35.99 to say 40.00 If the row is no longer in the rss feed, then I need to delete it from the db. Can anyone provide me a small snippet or any advice on how to go about doing this? I can provide the code I'm using to actually insert the rows from the RSS feed if it helps? Thanks Earlier I posted about how to best deal with a calculated field in MySQL and was advised not to store the value in the database. So, I took my calculated value out of the tables. I can select based on the calculated value
SELECT ... right_count, wrong_count, right_count + wrong_count AS Total_Count, right_count / (right_count + wrong_count) AS Right_Percent ...but now I want to UPDATE based on Right_Percent. The old code was: UPDATE leitner_vcard_boxes SET box=box+1 last_reboxed_date='$today' where box<7 AND last_reboxed_date<'$cutoff_date' and right_percent<='$score'The only way I can think of updating is something like: UPDATE leitner_vcard_boxes SET box=box+1 last_reboxed_date='$today' WHERE box<7 and last_reboxed_date<'$cutoff_date' AND Right_Percent IN (SELECT right_count, wrong_count, right_count / (right_count + wrong_count) AS Right_Percent FROM leitner_vcard_boxes WHERE Right_Percent,='$score');Will using a subquery even work? Is there a better way? To further complicate things, I loop through 5 score/cutoff date pairs (so that a lower score gets reboxed faster than a higher score). I imagine it would be more efficient to try to combine it all into a single query.
$cutoff_scores=array(25=>7,50=>14,75=>28,90=>40);
foreach ($cutoff_scores as $score => $rebox_interval){
$today=date('Y-m-d');
$interval=$rebox_interval.' days';
$cutoff_date=date_sub($today, date_interval_create_from_date_string($interval));
$query="UPDATE leitner_vcard_boxes SET box=box+1 last_reboxed_date='$today' where box<7 and last_reboxed_date<'$cutoff_date' and right_percent<='$score'";
$result= mysqli_query($link, $interval);
}
Hi, I am hoping someone can help me out with a slight issue I have with php and mySQL. I have an ajax-powered form with a select (dropdown) field populated through a php function. Based on the user-selected values in this field, data is displayed on the webpage; i.e. selected value 1 returns values x and y on the page. I am now trying to call additional data (value z) from a different table in the same database, and as before, use the selected values from the dropdown to display the data. For some reason, value z is not changing according to the user-selected value. This is my code: [The function to populate the select field] Code: [Select] function kfl_get_funds_names() { $result = array(); $result['CDF'] = 'Crosby Dragon Fund'; $result['CPF'] = 'Crosby Phoenix Fund'; $result['AMZPIF'] = 'AMZ Plus Income Fund'; $result['KASBIIF'] = 'KASB Islamic Income Opportunity'; $result['KASBCPGF'] = 'KASB Capital Protected Gold Fund'; $result['KASBLF'] = 'KASB Income Opportunity Fund'; $result['KASBCF'] = 'KASB Cash Fund'; $result['KASBBF'] = 'KASB Asset Allocation Fund'; $result['KASBSMF'] = 'KASB Stock Market Fund'; return $result; } [the code calling and using the function to interact with the database] Code: [Select] $funds_to_display = kfl_get_funds_names(); $current_symbol = key( $funds_to_display ); $current_nav_rates = kfl_get_latest_rates( $current_symbol ); [the code calling additional data, value z, from the database, and using the info in the select field to filter it] Code: [Select] $cutoff = kfl_cutoff( $current_symbol ); The display of each of these items is as follows: Code: [Select] <?php echo $current_nav_rates['nav_date']; ?> <?php echo $funds_to_display[$current_symbol]; ?> <?php echo $cutoff['cutoff']; ?> I can't get the $cutoff code to display the correct values. It picks up the first symbol to display and doesn't change with user selection. The code for the selection box, by the way: Code: [Select] <select id="dailynav-funds" autocomplete="off" name="dnf"> <?php foreach ($funds_to_display as $fund_symbol => $fund_name) { echo '<option'; if( $fund_symbol == $current_symbol ) { echo ' selected="selected"'; } echo ' value="' . $fund_symbol . '">'; echo $fund_name; echo '</option>'; } ?> </select> I've tried to get data using $_GET['dnf'] into the cutoff code, but that throws up parse errors. What am I doing wrong, and how can I resolve this issue? Thanks in advance! I'm not sure what to search for or if this can be done... Let's say I have a mysql table named "genre". Now I have "Male" and "Female" in a dropdown menu like this in a form: <select name="genre"> <option>Male</option> <option>Female</option> </select> How would you display, for example, the option Female in a update.php file if that's the genre stored in the mysql database when you fetch the results? I have a table with two columns. The first is an id column, set to be my primary key, non null, unique, int, and auto increment. The second is a varchar(10) column. Very strangely, when I set the varchar(10) column to unique, and try to edit the table in workbench, the table is automatically ordered by my varchar column, and not my id (primary key column). If I mark the varchar(10) column as not unique, data is ordered by the primary key, as expected. What is going on here? I expected the primary key to be default ordering for the table. I hope to not use an ORDER BY clause.
Edited by E_Leeder, 29 November 2014 - 02:14 AM. Hello there, I'm trying to show a picture based on the value that was chosen in the dropdown menu of the page before the submit. Let's say i'm having a drop down form with 3 values: Porsche, BMW, Audi, the form also consists of a button to submit form and some other text fields but these aren't really relevant. So what i want is, you choose whatever car, let's say BMW, fill in all the other data of the form, hit submit, and on the next page it should show a Picture which i define for each car. I hope i explained that somehow understandable. Thanks in advance, Sabine I have 10 tables in my database. I want load these 10 table names into a drop-down list. If a user selects a value form the drop-down list, a form should be displayed with related to the table. (form fields should be appropriate table's column headers) When user fill the form and submitted, data should be saved in the selected table. Give me a code example to do this. I'm new to this form and php/mysql so sorry if this isn't the right place to post this. This is what is in the first dropdown box. Code: [Select] $selValues['john'] = "a, b, c, d, e"; These are the different lists I want it to put in the second drop down box depending on what they choose in the first. Code: [Select] $selValues['list1']= " a1, a2, a3, a4, a5"; $selValues['list2']= " b1, b2, b3, b4, b5"; This is how I made an attempt to make it work before posting here. Along with plently of other ways. Code: [Select] if ($selValues['john']=a) { $chan_input = selectBuilder($selValues['$list1'] }; else if ($selValues['john']=b) { $chan_input = selectBuilder($selValues['$list2'] }; Basicly how I need it to work is there are two drop down boxes. First they will chose between a,b,c,d,e. If they chose a then on the second drop down box I only want them to be able to select a1,a2,a3,a4,a5. I'm new to PHP and I was able to figure out how to populate data from my database into my text fields. I am trying to add the update information to the same php file; however, I am now receiving errors within the data I was able to populate,
Notice: Undefined variable: stmt in C:\xampp\htdocs\Cust_App\update.php on line 47 and errors with the Update statement
Notice: Undefined variable: stmtupdate in C:\xampp\htdocs\Cust_App\update.php on line 96 I had defined the customerID variable above in the code, but it isn't being captured from here. I tried setting up this query like the one which gathered the data, but I'm off by a little bit. I would like the option to be able to update all fields. Any help is appreciated. I'm trying to learn as I may get asked to update other forms in the future. (new boss asks a lot)
<?php
require_once('database.php');
?>
<!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">
<!-- the head section -->
<head>
<title>My Guitar Shop</title>
<link rel="stylesheet" type="text/css" href="main.css" />
</head>
<!-- the body section -->
<body>
<div id="page">
<div id="header">
<h1>SportsPro Technical Support</h1>
<p>Sports management software for the sports enthusiast.</p></h1>
</div>
<div id="main">
<h1>View/Update Customer</h1>
<form action="update.php" method="get" >
<?php
if(isset($_GET['customerID'])) {
$customerID = filter_input(INPUT_GET, 'customerID', FILTER_SANITIZE_NUMBER_INT);
$sql = "SELECT * FROM customers WHERE customerID =$customerID ";
$stmt = $db->query($sql);
}
?>
<div id="content">
<!-- display a table of products -->
<h2>Customers</h2>
<form method = "edit">
<?php foreach ($stmt as $cust) { ?>
<div>
<label>First Name</label>
<input type="text" name="name" class ="form-control" value ="<?php echo $cust['firstName']; ?>">
</div><br>
<div>
<label>Last Name</label>
<input type="text" name="name" class ="form-control" value ="<?php echo $cust['lastName']; ?>">
</div><br>
<div>
<label>Address</label>
<input type="text" name="address" class ="form-control" value ="<?php echo $cust['address']; ?>">
</div><br>
<div>
<label>City</label>
<input type="text" name="city" class ="form-control" value ="<?php echo $cust['city']; ?>">
</div><br>
<div>
<label>State</label>
<input type="text" name="state" class ="form-control" value ="<?php echo $cust['state']; ?>">
</div><br>
<div>
<label>Country</label>
<input type="text" name="countryCode" class ="form-control" value ="<?php echo $cust['countryCode']; ?>">
</div><br>
<div>
<label>Zip Code</label>
<input type="text" name="postalCode" class ="form-control" value ="<?php echo $cust['postalCode']; ?>">
</div><br>
<div>
<label>Email </label>
<input type="text" name="email" class ="form-control" value ="<?php echo $cust['email']; ?>">
</div><br>
<div>
<label>Phone Number </label>
<input type="text" name="phone" class ="form-control" value ="<?php echo $cust['phone']; ?>">
</div><br>
<div>
<label>Password </label>
<input type="text" name="password" class ="form-control" value ="<?php echo $cust['password']; ?>">
</div><br>
<div>
<?php }
?>
<input type="Submit" name="Update_Data" value="Update Data"></input>
<?php
$sql2 = "UPDATE customers SET firstName = ". $stmtupdate['firstName']." WHERE customerID =$customerID ";
$stmtupdate = $db->query($sql2);
?>
</div>
</div>
<div id="footer">
<p>
© <?php echo date("Y"); ?> SportsPro, Inc.
</p>
</div>
</div><!-- end page -->
</body>
</html>
Yesterday, I created a topic about how I could update records and I managed to achieve that successfully. Now I have another dilemma. When I have a specific record I want to update, I want to change a category ID of an product (e.g. change it from 1 to 2) but how do I go about doing this? Here is my code thus far: Code: [Select] <?php require_once ('./includes/config.inc.php'); require_once (MYSQL); $id=$_GET['prodID']; $results = mysqli_query($dbc, "SELECT * FROM product WHERE prodID=".$_GET['prodID'].""); $row = mysqli_fetch_assoc($results); ?> <form action="" method='POST'> Product ID: <input type="text" value="<?php echo $row['prodID'];?>" name="prodID" /> <br /> Product: <input type="text" value="<?php echo $row['product'] ;?>" name="product" /> <br /> Product Description: <input type="text" value="<?php echo $row['prod_descr'] ;?>" name="prod_descr" /> <br /> Category: <select name="category"> <option value="<?php echo $row['catID'];?>"></option> </select> Price: <input type="text" value="<?php echo $row['price'] ;?>" name="price" /> <br /> In Stock: <input type="text" value="<?php echo $row['stock'] ;?>" name="stock" /> <br /> <br /><input type="submit" value="save" name="save"> </form> <?php if(isset($_POST['save'])) { $id = $_POST['prodID']; $product = $_POST['product']; $descr = $_POST['prod_descr']; $price = $_POST['price']; $stock = $_POST['stock']; // Update data $update = mysqli_query($dbc, "UPDATE product SET product='$product', prod_descr='$descr', price='$price', stock='$stock' WHERE prodID=".$_GET['prodID'].""); header( 'Location: update_save.php' ) ; } ?> I am working on a project that uses a drop down list to chose the category when inserting new data into the database. What I want to do now is make the drop down list default to the chosen category on the list records page and the update page. I have read several tutorials, but they all say that I have to list the options and then select the default. But since it is possible to add and remove categories, this approch won't work. I need the code to chose the correct category on the fly. There are two tables, one that has the category ID and category name. The second table has the data and the catid which is referenced to the category id in the first table. Code: [Select] -- -- Table structure for table `categories` -- DROP TABLE IF EXISTS `categories`; CREATE TABLE IF NOT EXISTS `categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `categories` varchar(37) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=40 ; -- -------------------------------------------------------- -- -- Table structure for table `links` -- DROP TABLE IF EXISTS `links`; CREATE TABLE IF NOT EXISTS `links` ( `id` int(4) NOT NULL AUTO_INCREMENT, `catid` int(11) DEFAULT NULL, `name` varchar(255) NOT NULL DEFAULT '', `url` varchar(255) NOT NULL DEFAULT '', `content` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `catid` (`catid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ; Then is the list records file, I have Code: [Select] <?php include ("db.php"); include ("menu.php"); $result = mysql_query("SELECT categories FROM categories") or die(mysql_error()); while ($row = mysql_fetch_array($result)) { $categories=$row["categories"]; $options.= '<option value="'.$row['categories'].'">'.$row['categories'].'</option>'; }; $id = $_GET['id']; $query="SELECT * FROM links ORDER BY catid ASC"; $result=mysql_query($query); ?> <table width="65%" align="center" border="0" cellspacing="1" cellpadding="0"> <tr> <td> <table width="100%" border="1" cellspacing="0" cellpadding="3"> <tr> <td colspan="7"><strong>List data from mysql </strong> </td> </tr> <tr> <td align="center"><strong>Category ID</strong></td> <td align="center"><strong>Category ID</strong></td> <td align="center"><strong>Name</strong></td> <td align="center"><strong>URL</strong></td> <td align="center"><strong>Content</strong></td> <td align="center"><strong>Update</strong></td> <td align="center"><strong>Delete</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td> <SELECT NAME=catid> <OPTION>Categories</OPTION> <?php echo $options; ?> </SELECT> </td> <td><? echo $rows['catid']; ?></td> <td><? echo $rows['name']; ?></td> <td><a href="<? echo $rows['url']; ?>"><? echo $rows['url']; ?></a></td> <td><? echo $rows['content']; ?></td> <td align="center"><a href="update.php?id=<? echo $rows['id']; ?>">update</a></td> <td align="center"><a href="delete.php?id=<? echo $rows['id']; ?>">delete</a></td> </tr> <?php } ?> </table> </td> </tr> </table> <?php mysql_close(); ?> So, how do I get this code Code: [Select] $result = mysql_query("SELECT categories FROM categories") or die(mysql_error()); while ($row = mysql_fetch_array($result)) { $categories=$row["categories"]; $options.= '<option value="'.$row['categories'].'">'.$row['categories'].'</option>'; }; <SELECT NAME=catid> <OPTION>Categories</OPTION> <?php echo $options; ?> </SELECT> to give me an output that will be something like if catid exactly matches categories.id echo categories.categorie ??? so far everything I have done produces either a default category of the last category, the catid (which is a number), all of the categories (logical since catid will always be = id, or nothing. How do I get just the category name? I will keep reading and try to figure this out, but any help would be greatly appreciated. Thanks in advance Hi All ,
I have a small table with 4 fields namely Day_ID, Dues, Last_Visit, Points. where Day_ID is an auto-increment field. The table would be as follows:
Day_ID -- Dues --- Last_Visit --- Points.
1 --------- 900 -------- 1/12 -------- 6
2 --------- 700 -------- 4/12 -------- 7
3 --------- 600 -------- 7/12 -------- 5
4 --------- 600 -------- 9/12 -------- 6
5 --------- 600 -------- 10/12 ------- 6
6 --------- 600 -------- 14/12 ------- 6
So this is the record of a person's visit to say a club. The last row indicates the last date of his visit to the club. His points on this date are 6. Based on this point value of 6 in the last row I want to retrieve all the previous BUT adjoining all records that have the same Points i.e. 6.
So my query should retrieve for me, based on the column value of Points of the last row (i.e. Day_ID - 6 ), as follows:
4 --------- 600 -------- 9/12 -------- 6
5 --------- 600 -------- 10/12 ------- 6
6 --------- 600 -------- 14/12 ------- 6
This problem stated above had been completely resolved, thanks to a lot of help from Guru Barand by this following query :-
$query = "SELECT cv.day_id, cv.dues, cv.last_visit, cv.points FROM clubvisit cv WHERE last_visit >= ( SELECT MAX(last_visit) FROM clubvisit WHERE points <> ( SELECT points as lastpoints FROM clubvisit JOIN ( SELECT MAX(last_visit) as last_visit FROM clubvisit ) as latest USING (last_visit) ) )";I am using this and it works perfectly except that now there is a slight change in the table because the criteria for points is now dependent on more than one column cv.points and is more like cv.points1, cv.points2, cv.points3 etc. So now I need to make a selection based on each of these cv.points columns. As of now I can still get the results by running the query multiple times for each of the cv.points columns ( seperately for cv.points1, cv.points2, cv.points3) and it works correctly. However I am wondering if there is a better way to do this in just one go. This not only makes the code repetitive but also since the queries are interconnected, involves the use of transactions which I wish to avoid if possible. The values that I require for each of the cv.point columns is 1. day_id of the previous / old day on which the cv.points value changed from the current day value, and 2. cv.points on that old/ previous day. So for example if the table is as below: Day_ID -- Dues --- Last_Visit --- Points1 --- Points2. 1 --------- 900 -------- 1/12 ----------- 9 ------------ 5 2 --------- 600 -------- 4/12 ----------- 6 ------------ 6 3 --------- 400 -------- 7/12 ----------- 4 ------------ 7 4 --------- 500 -------- 9/12 ----------- 5 ------------ 8 5 --------- 600 -------- 10/12 ---------- 6 ------------ 8 6 --------- 600 -------- 11/12 ---------- 6 ------------ 8 7 --------- 600 -------- 13/12 ---------- 6 ------------ 7 8 --------- 500 -------- 15/12 ---------- 5 ------------ 7 9 --------- 500 -------- 19/12 ---------- 5 ------------ 7 Then I need the following set of values : 1. day_id1 -- Day 7, points1 ---- 6, days_diff1 -- (9-7 = 2) . // Difference between the latest day and day_id1 2. day_id2 -- Day 6, points2 ---- 8, days_diff2 -- (9-6 = 3) 3. day_id3 -- .... and so on for other points. Thanks all ! This is a little confusing so please bear with me. I was thinking of using left.join but couldn't figure out how to implement it properly. I am trying to order all of the results from a table names 'clans' based on how many points the clan has. To calculate the points you have to go into another table 'clanteams' and then loop every team in the clan pulling wins and losses from a row in a specific ladder table. Here is the code i have to calculate the points. Code: [Select] $total[wins] = 0; $total[loss] = 0; $members=mysql_query("SELECT id,clanid,teamid,DATE_FORMAT(datejoined,'%M %d, %Y') FROM clanteams WHERE clanid='$member[id]'"); while(list($id,$clanid,$teamid,$joined)=mysql_fetch_row($members)){ $team=mysql_query("SELECT name,ladderid FROM teams WHERE id='$teamid'"); if(mysql_num_rows($team) == 0) continue; $team=mysql_fetch_array($team); $ladder=mysql_query("SELECT name FROM ladders WHERE id='$team[ladderid]'"); $ladder=mysql_fetch_array($ladder); $linfo=mysql_query("SELECT rank,wins,loss FROM ladder_$team[ladderid] WHERE teamid='$teamid'"); $linfo=mysql_fetch_array($linfo); $total[wins] = $total[wins] + $linfo[wins]; $total[loss] = $total[loss] + $linfo[loss]; } $totalpoints = ($total[wins] * 2) - $total[loss]; So now i want to loop through every row in the clans table, and using the above code oder them by $total points. Ive spent hours wrapping my head around it and still cannot figure it out. Please help. Hello everybody,
I want to obtain from the database the rows that have been inserted in the last seven days.
Every time that I insert a row, I add a date with this format:
$today = date("Y-m-d H:i:s");
So it should be like:
$lastweek = date("Y-m-((d)-7) H:i:s");
SELECT * FROM pub WHERE data BETWEEN('$today' AND '$lastweek')
Is it possible? which is the method to do that?
Thank you all
|
|||||||