|
PHP - Form To Update Table Results
Hi Guys.
I have some code which displays the rows of a database table in a html table on my webpage: Code: [Select] <?php//to display image from source$dir = "360_covers";echo '<table>';echo '<tr><th>Title</th><th>Cover</th><th>comment</th></tr>';$result = mysql_query("SELECT * FROM xbox_games order by gametitle");while ($row = mysql_fetch_assoc($result)) { echo "<tr><td>{$row['gametitle']}</td><td><img src=\"$dir/{$row['cover']}\" width='38' height='38'></td><td>{$row['cover']}</td></tr>";}//close out tableecho '</table>';?> What i want to do now is add a form, possibly at the end of each row of the html table, which will allow a session user to update that record. Can anybody advise on the best way to do this? Similar TutorialsHey. Third question today. Very stuck on this one. I am displaying a ton of while loops to display a bunch of data from two different tables. there is NO possible way i can combine the two tables. I mean, I can, but I'm not going to because they are two totally different thins. Anyway. Here is what I'm trying to do: Lets say I have 3 items, a grape, an orange and an apple. - 3 apples - 2 oranges - 1 grape I'm displaying an update price box for each different fruit. (so 3 different boxes), and one submit button. I update it, and it does update the price but it only updates the very first row. I want to be able to update every row I types the price in for. Here is my code: (it is quite long): Code: [Select] <?php session_start(); include("config536.php"); ?> <html> <head> <link rel="stylesheet" type="text/css" href="style.css" /> </head> <?php if(!isset($_SESSION['username'])) { echo "<banner></banner><nav>$shownavbar</nav><ubar><a href=login.php>Login</a> or <a href=register.php>Register</a></ubar><content><center><font size=6>Error!</font><br><br>You are not Logged In! Please <a href=login.php>Login</a> or <a href=register.php>Register</a> to Continue!</center></content>"; } if(isset($_SESSION['username'])) { echo "<nav>$shownavbar</nav><ubar>$ubear</ubar><content><center><font size=6>User Shops</font><br><br>"; $action = $_GET['action']; if(!isset($action)) { echo "<a href=?action=edit>Edit Shop</a> | <a href=?action=view&user=$showusername>View Shop</a> | <a href=?action=stock>View Stock</a> | <a href=?action=quick>Quick Stock</a><br><br>"; } if(isset($action)) { $getshopq = "SELECT * FROM usershops WHERE owner='$showusername'"; $getshop = mysql_query($getshopq); while($tri = mysql_fetch_array($getshop)) { $theid = $tri['ushopid']; $owner = $tri ['owner']; $desc = $tri['description']; $image = $tri['image']; $name = $tri['name']; $status = $tri['status']; $discount = $tri['discount']; } $countq = "SELECT * FROM usershops WHERE owner='$showusername'"; $countit = mysql_query($countq); if($action == "edit") { echo "<a href=?action=edit>Edit Shop</a> | <a href=?action=view&user=$showusername>View Shop</a> | <a href=?action=stock>View Stock</a> | <a href=?action=quick>Quick Stock</a><br><br><font size=5>Edit Shop</font><br><br>"; if(mysql_num_rows($countit)) { ?> <form action="<?php echo "$PHP_SELF"; ?>" method="POST"> <?php } if(!mysql_num_rows($countit)) { $create = $_POST['create']; $thename = $_POST['thename']; $thedesc = $_POST['thedesc']; $thestatus = $_POST['thestatus']; $theimage = $_POST['theimage']; if(!isset($create)) { ?> <form action="<?php echo "$PHP_SELF"; ?>" method="POST"> Shop Name: <input type="text" name="thename"><br>Description<br><textarea name="thedesc" rows="5" cols="25"></textarea><br> Status: <select name="thestatus"> <option>Open</option> <option>Closed</option> <option>Gallery</option> </select><br><br> <input type="submit" name="create" value="Create Shop"></form> <?php } if(isset($create)) { if($thename == "" || $thedesc == "") { echo "<font color=red>Error! Please fill out the entire form!</font><br><br>"; ?> <form action="<?php echo "$PHP_SELF"; ?>" method="POST"> Shop Name: <input type="text" name="thename"><br>Description<br><textarea name="thedesc" rows="5" cols="25"></textarea><br> Status: <select name="thestatus"> <option>Open</option> <option>Closed</option> <option>Gallery</option> </select><br><br> <input type="submit" name="create" value="Create Shop"></form> <?php } if($thename != "" && $thedesc != "") { mysql_query("INSERT INTO usershops (owner, name, description, image, status, discount) VALUES ('$showusername', '$thename', '$thedesc', 'image.gif', '$thestatus', 'None')"); echo "<font color=green>Success! You have created your shop! Click <a href=?action=view>here</a> to view it.</font><br><br>"; } } } } if($action == "view") { echo "<a href=?action=edit>Edit Shop</a> | <a href=?action=view&user=$showusername>View Shop</a> | <a href=?action=stock>View Stock</a> | <a href=?action=quick>Quick Stock</a><br><br><font size=5>Shop Name Here</font><br><br>"; $wowq = "SELECT * FROM uitems WHERE username='$showusername' AND location='2'"; $wow = mysql_query($wowq); while($wrow = mysql_fetch_array($wow)) { $umid = $wrow['uitemid']; $umiid = $wrow['theitemid']; $umloc = $wrow['location']; $umprice = $wrow['price']; $infoq = "SELECT * FROM items WHERE itemid='$umiid'"; $info = mysql_query($infoq); while($row = mysql_fetch_array($info)) { $myname = $row['name']; $myid = $row['itemid']; $myimage = $row['image']; $mydesc = $ow['description']; $myrarity = $row['rarity']; echo "$myname"; } } } if($action == "stock") { $setprice = $_POST['price']; $updateprice = $_POST['updateprice']; echo "<a href=?action=edit>Edit Shop</a> | <a href=?action=view&user=$showusername>View Shop</a> | <a href=?action=stock>View Stock</a> | <a href=?action=quick>Quick Stock</a><br><br><font size=5>Stock Shop</font><br><br>"; $eq = "SELECT * FROM uitems WHERE username='$showusername' AND location='2' GROUP BY theitemid"; $ee = mysql_query($eq); while($erow = mysql_fetch_array($ee)) { $eeloc = $erow['location']; $eeid = $erow['theitemid']; $eenowid = $erow['uitemid']; $eeprice = $erow['price']; $wq = "SELECT * FROM items WHERE itemid='$eeid'"; $ww = mysql_query($wq); while($wrow = mysql_fetch_array($ww)) { $cq = mysql_query("SELECT * FROM uitems WHERE username='$showusername' AND location='2' AND theitemid='$eeid'"); $lcq = mysql_num_rows($cq); $fid = $wrow['itemid']; $fname = $wrow['name']; $fimage = $wrow['image']; $frarity = $wrow['rarity']; $fdesc = $wrow['description']; echo "$fname<br><img src=/images/items/$fimage>"; ?> <form action="<?php echo "$PHP_SELF"; ?>" method="POST"> <input type="text" name="price" maxlength="5"><br><br> <?php } } if(isset($updateprice)) { mysql_query("UPDATE uitems SET price='$setprice' WHERE username='$showusername' AND theitemid='$fid'"); echo "<font color=green>Success! Your prices have been set and updated!</font>"; } ?> <input type="submit" name="updateprice" value="Update Prices"> <br> <?php } if($action == "quick") { echo "<a href=?action=edit>Edit Shop</a> | <a href=?action=view&user=$showusername>View Shop</a> | <a href=?action=stock>View Stock</a> | <a href=?action=quick>Quick Stock</a><br><br><font size=5>Quick Stock</font><br><br>"; } } } ?> </html> To help you find it, I'm asking about the "stock" $_GET part, about three quarters down. Thanks in advance for anybody who can help me, I've been stuck on this script all day! Thanks again! Can anyone post a generic update function to update mysql table. The manual approach: update $tablename set $column1='a', $column2='b' where $id=$value; Hi i have this simple update form and scrip but somehow it doesnt seem to be update the field on the database can someone help out please. The html form is the second form bellow where the action send to status_update.php HTML FORM Code: [Select] <?php include("../header.html"); ?> <?php include("header_news.html"); extract($_REQUEST,EXTR_SKIP); ?><?php /* print("sfilm_refnum = $sfilm_refnum<BR>"); print("sfilm_addr01 = $sfilm_addr01<BR>"); print("sfilm_postcode = $sfilm_postcode<BR>"); print("Film Client = $fclient<BR>"); */ ?> <form id="search" action="list.php" method="post" name="search"> <table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#eeeeee"> <tr> <td align="right" width="140"></td> <td width="320"><span class="hofblack10"> </span> </td> <td align="center" width="100"><input type="hidden" name="lstart" value="<?php print("$lstart"); ?>" /><input type="hidden" name="lend" value="<?php print("$lend"); ?>" /><input type="hidden" name="lamount" value="<?php print("$lamount"); ?>" /></td> <td align="center" width="100"></td> <td align="right"></td> </tr> </table> </form> <table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#4050c4"> <tr> <td width="60" class="hofwhite10">action</td> <td width="140"><span class="hofwhite14">DATE</span></td> <td width="80"><span class="hofwhite14">ID</span></td> <td><span class="hofwhite14">News Titile</span></td> <td width="100"><span class="hofwhite14">Status</span></td> </tr> <tr height="0"> <td bgcolor="white" width="60"></td> <td bgcolor="white" width="140" height="0"></td> <td bgcolor="white" width="80" height="0"></td> <td bgcolor="white" height="0"></td> <td bgcolor="white" width="100" height="0"></td> </tr> </table><table width="780" border="0" cellspacing="0" cellpadding="4"><tr> <td width="60"></td> <td width="80"></td> <td><a class="blueullrg" href="add.php">Add News</a></td> <td align="right" width="120"></td> </tr> <tr height="0"> <td width="60" height="0"></td> <td width="80" height="0"></td> <td height="0"></td> <td align="right" width="120" height="0"></td> </tr> </table> <?php //get the DB connection variables include("../../../includes/config.php"); //connect to DB $connection = @mysql_connect($db_address,$db_username,$db_password) or die("Couldn't CONNECT."); $db = @mysql_select_db($db_name, $connection) or die("Couldn't select DATABASE."); $query2="SELECT * FROM news WHERE !(news_status='deleted')"; $result2 = mysql_query($query2) or die("Couldn't execute QUERY - Select NEWS Qty"); $fqty = mysql_num_rows($result2); //SELECT or FIND the same USERNAME $query3="SELECT * FROM news WHERE !(news_status='deleted') ORDER BY news_id DESC"; $result3 = mysql_query($query3) or die("Couldn't execute QUERY - Select NEWS"); while ($row = mysql_fetch_array($result3)) { $news_id = $row['news_id']; $news_title = $row['news_title']; $news_story = $row['news_story']; $news_image = $row['news_image']; $news_image_caption = $row['news_image_caption']; $news_image_link = $row['news_image_link']; $news_date_day = $row['news_date_day']; $news_date_month = $row['news_date_month']; $news_date_year = $row['news_date_year']; $news_status = $row['news_status']; $news_website = $row['news_website']; $news_date_created = $row['news_date_created']; $news_date_modified = $row['news_date_modified']; ?> <table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#eeeeee"> <tr> <td width="60"><span class="hofblack10"> <?php if($news_status=="deleted"){ print("<a class='hifblack10'>deleted</span>"); }ELSE{ print("<a class='blueul' href='edit.php?id=$news_id'>edit</a>"); } ?> </span></td> <td width="140"><span class="titlegrey12"> <?php if(!$news_date_day) { echo "00"; } else{ echo $news_date_day; } echo "/"; if(!$news_date_month) { echo "00"; }else{ echo $news_date_month; } echo "/"; if(!$news_date_year) { echo "0000"; }else{ echo $news_date_year; } ?> </span></td> <td width="80"><span class="titlegrey12"><?php print("$news_id"); ?></span> </td> <td><?php if($news_status=="deleted") { print("<class='hofblack10'>$news_title</span>"); }ELSE{ print("<a class='blueul' href='edit.php?id=$news_id'>$news_title</a>"); } ?></td> <td width="100"> <form id="list_update" action="status_update.php" method="post" name="list_update"> <select name="newnstatus" size="1"> <option <?php if($row['news_status'] == "") { print("selected"); } ?> selected="selected" value="">Status...</option> <option <?php if($row['news_status'] == "on") { print("selected"); } ?> value="on">On</option> <option <?php if($row['news_status'] == "off") { print("selected"); } ?> value="off">Off</option> <option <?php if($row['news_status'] == "deleted") { print("selected"); } ?> value="deleted">Delete</option> </select> <input type="hidden" name="nstatus" value="<?php echo $row[news_status]; ?>" /> <input type="hidden" name="id" value="<?php echo $row[news_id]; ?>" /> <input type="submit" name="update" value="update" /> </form> </td> </tr> <tr height="0"> <td bgcolor="white" width="60"></td> <td bgcolor="white" width="140" height="0"></td> <td bgcolor="white" width="80" height="0"></td> <td bgcolor="white" height="0"></td> <td bgcolor="white" width="100" height="0"></td> </tr> </table> <?php } mysql_close($connection);//}?> <table width="780" border="0" cellspacing="0" cellpadding="4"> <tr> <td width="60"></td> <td width="80"></td> <td><a class="blueullrg" href="add.php">Add News</a></td> <td align="right" width="120"></td> </tr> </table><?php // include("list_navigation.html"); ?> <?php include("../footer.html"); ?> </div></body></html> The action script php Code: [Select] <?php /* echo "fstatus: ".$fstatus."<BR>"; echo "id: ".$id."<BR>"; echo "fclient: ".$fclient."<BR>"; echo "newfstatus: ".$newfstatus."<BR>";*/ //set the date of agreement $timestamp = date('l jS \of F Y h:i:s A'); //get the DB connection variables include("../../../includes/config.php"); //connect to DB $connection = @mysql_connect($db_address,$db_username,$db_password) or die("Couldn't CONNECT."); $db = @mysql_select_db($db_name, $connection) or die("Couldn't select FILMS DATABASE."); // All appears well, so enter into database $query= "UPDATE news SET news_status = '$newnstatus' WHERE news_id='$id'"; $result = mysql_query($query) or die("could not execute query - Update FILMS Record to DB"); //setup an email to the Admin @ hof, w/o attachment $emailto="xx@xxx.co.uk"; $emailfrom="no-reply@xxxx.co.uk"; $emailsubject="xx Record Updated"; $emailmessage="Hello Registrar\n\n"; $emailmessage.="News ID: ".$id."\n"; $emailmessage.="Updated on: ".$timestamp."\n\n"; $emailmessage.="Status was: ".$nstatus."\n"; $emailmessage.="Status now: ".$newfnstatus."\n"; $emailmessage.="Thank you,\n\n"; $emailmessage.="Web Site ROBOT\n"; $emailmessage.="(Administrator)\n"; $emailmessage.="xxx.co.uk | xxx.biz\n"; $emailmessage.="----------------------------------------------\n"; $emailmessage.="e. http://www.xxx.co.uk/contact.php\n"; $emailmessage.="w. http://www.xxx.co.uk\n"; $emailheader="From: xxx.co.uk<$emailfrom>"; $emailheader .= 'Cc: xxx@xxx.co.uk'."\r\n"; $emailheader .= 'Bcc: xxx@xxxxx.co.uk'."\r\n"; $ok=mail($emailto,$emailsubject,$emailmessage,$emailheader); mysql_close($connection); if ($ok) { header("Location: list.php"); /* Redirect browser */ exit; } else { $errmsg="There was a problem, please try later or telephone us direct."; $errsta="1"; include("edit_error.html"); //echo "<p>Mail could not be sent. Sorry!</p>"; exit; } ?> Thanks in advance Hi all, Im really struggling to build an update multiple records form, Ive built my table which calls all data i need from the db in a looped region which works fine I just cant understand how to then input these back into the table in the correct rows, if this makes sense? Ive been reading this tutorial but Its not very informative its more of a copy and paste http://www.phpeasystep.com/mysql/10.html 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>
I'm not looking for code, mostly just some direction, but I'm sure I'll be back as I try to shape the code. Also, I know very little about coding jQuery, Ajax, etc. Here is what I'm looking for: Basketball coach enters a Page that only he (or admin) can see. (I have that part figured out.) Once there, he has a form to fill out with a player's name and other information. I'd rather there not be 12 rows of empty cells. (Coaches will view that as work) I'd rather not have the Page reload on each "Add", but that would be the lesser of two evils. Is there a way to keep a persistent connection so when a coach hits Add, the player gets added to the database, a new empty form shows up, and the player's information shows up below on a roster? Good morning. I am looking fo some help with an update script to update all rows in a table. This is what I have for a form and all looks well. form.php <?php // run the query and put the results in an array variable called $result $result = mysql_query("SELECT * FROM table ORDER BY 'id', 'title', 'text', 'number'"); print "<form method='post' action='update.php'> <table width='100%' border='0' cellspacing='1' cellpadding='1'><tr> <td align='center'><strong>ID</strong></td> <td align='center'><strong>Title</strong></td> <td align='center'><strong>text</strong></td> <td align='center'><strong>Number</strong></td> </tr>\n"; // start a loop to print all of the courses with their book 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)) { // start displaying the info; the most important part is to make the name an array (notice bookinfo[$i]) print "<td align='center'><p>{$Update['id']}</p></td>\n"; print "<td align='center'><input type='text' name='title' value='{$Update['title']}' /></td>"; print "<td align='center'><input type='text' size='40' name='text' value='{$Update['text']}' /></td>\n"; print "<td align='center'><input type='text' size='40' name='number' value='{$Update['number']}' /></td>\n"; print "</tr>\n"; // add 1 to the count, close the loop, close the form, and the mysql connection } print "<tr> <td colspan='4' align='center'><input type='submit' value='submit' />"; print "</td> </tr> </table> </td> </tr> </form> </table>"; print "</tr>\n"; ?><br /><br /> My question is. How do I update this info into the database with the proper info. ie. Update.php? Hello I have three tables User diysmps usergroup usergroupid is common in the three tables. diysmps is joined on usergroup, so running this sql SELECT diysmps.name, diysmps.email, diysmps.sumbit, diysmps.message, diysmps.ID, usergroup.title FROM diysmps INNER JOIN usergroup ON diysmps.usergroupid = usergroup.usergroupid ORDER BY diysmps.ID DESC see results i get in picture attached. the result (PENDIN REGISTRATION) is what i need to be updated WHEN the user register to the forums. when the user register to the forums, an account with his EMAIL will be created into the (USER) table. So, I need to look out for the email FROM (diysmps TABLE) into the user table _search user table for email from diysmps table_ and if found to update the USERGROUPID in (diysmps TABLE) withe the USERGROUPID from (USER) table (related to the email searched) so, when user registers, status from PENDING REGISTRATION to REGISTERED will be shown I know it looks complex for me, but thats how i can describe Thanks all Hello all,
Based on the suggestion of you wonderful folks here, I went away for a few days (to learn about PDO and Prepared Statements) in order to replace the MySQLi commands in my code. That's gone pretty well thus far...with me having learnt and successfully replaced most of my "bad" code with elegant, SQL-Injection-proof code (or so I hope).
The one-and-only problem I'm having (for now at least) is that I'm having trouble understanding how to execute an UPDATE query within the resultset of a SELECT query (using PDO and prepared statements, of course).
Let me explain (my scenario), and since a picture speaks a thousand words I've also inlcuded a screenshot to show you guys my setup:
In my table I have two columns (which are essentially flags i.e. Y/N), one for "items alreay purchased" and the other for "items to be purchased later". The first flag, if/when set ON (Y) will highlight row(s) in red...and the second flag will highlight row(s) in blue (when set ON).
I initially had four buttons, two each for setting the flags/columns to "Y", and another two to reverse the columns/flags to "N". That was when I had my delete functionality as a separate operation on a separate tab/list item, and that was fine.
Now that I've realized I can include both operations (update and delete) on just the one tab, I've also figured it would be better to pare down those four buttons (into just two), and set them up as a toggle feature i.e. if the value is currently "Y" then the button will set it to "N", and vice versa.
So, looking at my attached picture, if a person selects (using the checkboxes) the first four rows and clicks the first button (labeled "Toggle selected items as Purchased/Not Purchased") then the following must happen:
1. The purchased_flag for rows # 2 and 4 must be switched OFF (set to N)...so they will no longer be highlighted in red.
2. The purchased_flag for row # 3 must be switched ON (set to Y)...so that row will now be highlighted in red.
3. Nothing must be done to rows # 1 and 5 since: a) row 5 was not selected/checked to begin with, and b) row # 1 has its purchase_later_flag set ON (to Y), so it must be skipped over.
Looking at my code below, I'm guessing (and here's where I need the help) that there's something wrong in the code within the section that says "/*** loop through the results/collection of checked items ***/". I've probably made it more complex than it should be, and that's due to the fact that I have no idea what I'm doing (or rather, how I should be doing it), and this has driven me insane for the last 2 days...which prompted me to "throw in the towel" and seek the help of you very helpful and intellegent folks. BTW, I am a newbie at this, so if I could be provided the exact code, that would be most wonderful, and much highly appreciated.
Thanks to you folks, I'm feeling real good (with a great sense of achievement) after having come here and got the great advice to learn PDO and prepared statements.
Just this one nasty little hurdle is stopping me from getting to "end-of-job" on my very first WebApp. BTW, sorry about the long post...this is the best/only way I could clearly explaing my situation.
Cheers guys!
case "update-delete":
if(isset($_POST['highlight-purchased']))
{
// ****** Setup customized query to obtain only items that are checked ******
$sql = "SELECT * FROM shoplist WHERE";
for($i=0; $i < count($_POST['checkboxes']); $i++)
{
$sql=$sql . " idnumber=" . $_POST['checkboxes'][$i] . " or";
}
$sql= rtrim($sql, "or");
$statement = $conn->prepare($sql);
$statement->execute();
// *** fetch results for all checked items (1st query) *** //
$result = $statement->fetchAll();
$statement->closeCursor();
// Setup query that will change the purchased flag to "N", if it's currently set to "Y"
$sqlSetToN = "UPDATE shoplist SET purchased = 'N' WHERE purchased = 'Y'";
// Setup query that will change the purchased flag to "Y", if it's currently set to "N", "", or NULL
$sqlSetToY = "UPDATE shoplist SET purchased = 'Y' WHERE purchased = 'N' OR purchased = '' OR purchased IS NULL";
$statementSetToN = $conn->prepare($sqlSetToN);
$statementSetToY = $conn->prepare($sqlSetToY);
/*** loop through the results/collection of checked items ***/
foreach($result as $row)
{
if ($row["purchased"] != "Y")
{
// *** fetch one row at a time pertaining to the 2nd query *** //
$resultSetToY = $statementSetToY->fetch();
foreach($resultSetToY as $row)
{
$statementSetToY->execute();
}
}
else
{
// *** fetch one row at a time pertaining to the 2nd query *** //
$resultSetToN = $statementSetToN->fetch();
foreach($resultSetToN as $row)
{
$statementSetToN->execute();
}
}
}
break;
}
CRUD Queston.png 20.68KB
0 downloads
when I do a "SELECT * FROM system_disks WHERE system_id = 'aNumber'" and their is more than one result with that system_id, I get more than one result obviously. I essentially need two results in one row. I've took a screen shot of what it is doing and what I need it to do. The blurred row is a different system_id This is what the while ($row = mysql_fetch_array($query)) is currently doing: This is what I would like it to do: Sorry if this is a dumb question, I've been coding the past 48 hours and my brain is fried Hi, I'm quite new to this and I'm trying to get this to line up in a table with 3 columns (unlimited rows) I have searched and tried but I'm not having any luck. Can any one help? Thank you Code: [Select] <?php echo '<div class="resultados_sub_cat">'; foreach($this->subcats as $key => $subcat) { $subcat->link = JRoute::_('index.php?option=classcliff&view=list&catid='.$subcat->id."&Itemid=".$this->Itemid); if ($key != 0) echo ' - '; echo '<a href="'.$subcat->link.'">'.$subcat->name.'</a>'; } ?> Does anybody know how to show the below php results in a table format? <?php if(isset($_POST['submit'])){ if(isset($_GET['go'])){ $fname = $_POST['fname']; $lname = $_POST['lname']; $skill = $_POST['skill']; //connect to the database $db=mysql_connect ("127.0.0.1", "root", "") or die ('I cannot connect to the database because: ' . mysql_error()); //select the database to use $mydb=mysql_select_db("resource matrix"); //query the database table $sql="SELECT DISTINCT First_Name, Last_Name, l.Resource_ID FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '$fname' OR Last_Name LIKE '$lname' OR Skill_Name LIKE '$skill'"; //run the query against the mysql query function $result=mysql_query($sql); //create while loop and loop through result set while($row=mysql_fetch_array($result)){ $First_Name =$row['First_Name']; $Last_Name=$row['Last_Name']; $Resource_ID=$row['Resource_ID']; //display the result of the array echo "<ul>\n"; echo "<li>" . "<a href=\"a.php?id=$Resource_ID\">" .$First_Name . " " . $Last_Name . "</a></li>\n"; echo "</ul>"; } } else{ echo "<p>Please enter a search query</p>"; } } I have three tables: events, orderdetails & orders. First I query orderdetails to find all the records that match the EventID: $query1 = SELECT * FROM orderdetails WHERE EventID = $_SESSION['EventID']; This returns 4 records. These 4 records have a field called DetailOrderID which is the foreign key for orders.OrderID. Next I need to query the results of the first query to find all the records in the orders table that match up. For example: SELECT * from orders where $query1.DetailOrderID = orders.OrderID. How would I go about doing this? I'm head down the temporary table solution but wanted to through this one out for discussion before I invest too much time. i think this is more of a HTML problem, but it is in my PHP code so i will ask anyway! if i have the search code: if (count($error) < 1) { $searchSQL = "SELECT id, placing, racedate, horseid FROM race WHERE "; // grab the search types. $types = array(); $types[] = isset($_GET['placing'])?"`placing` LIKE '%{$searchTermDB}%'":''; $types[] = isset($_GET['racedate'])?"`racedate` LIKE '%{$searchTermDB}%'":''; $types[] = isset($_GET['horseid'])?"`horseid` LIKE '%{$searchTermDB}%'":''; $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked) if (count($types) < 1) $types[] = "`placing` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked $andOr = isset($_GET['matchall'])?'AND':'OR'; $searchSQL .= implode(" {$andOr} ", $types) . " GROUP BY `horseid` ORDER BY `racedate`"; // order by title. $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}"); if (mysql_num_rows($searchResult) < 1) { $error[] = "The search term provided {$searchTerms} yielded no results."; }else { $results = array(); // the result array $i = 1; echo '<table border="1"><tr><th>Num</th><th>Placing</th><th>Race Date</th><th>Horse ID</th></tr>'; while ($row = mysql_fetch_assoc($searchResult)) { echo "<tr><td>$i</td> <td>{$row['placing']}</td> <td><a href=\"viewrace.php?date=" . urlencode($row['racedate']) . "\">{$row['racedate']}</a></td> <td>{$row['horseid']}</td></tr>"; $i++; } echo '</table>'; how do i get the 'horseid' to appear at the header of the table rather than in a column? thank you for all the brilliant help this site provides!! Ive gotten some results user selcts check box on first page The php page will say which brackets it falls between example price is between 100-300 say I dont seem to be able to populate a table with the data in the database:S Code: [Select] $row_number= 0; while ($row = mysql_fetch_array($result3)) { If (($row["price"] = $price_low) && ($row["price"] <= $price_high)) //price If (($row["storage"] = $storage_low) && ($row["storage"] <= $storage_high)) //storage if (($row["Processor "] = $processor_low) && ($row["Processor"] <= $processor_high)) //Processor { $row_number++; ?> <tr> <td align="center"><?php print $row["Computer_Price"]; ?> </td> <td align="center"><?php print $row["Computer_Storage"]; ?> </td> <td align="center"><?php print $row["Computer_ProcessorSpeed"]; ?> </td> </tr> </table> Thats wat im usign atm the price_low and price_high are what sets the low and high price for the search Hi guys, I am working with an old script at the moment, there is one page which just will not populate the table results. I have tried running multiple debugging commands but the only one it flags is the line displaying Quote last; saying it's not a used function. If I comment out this line, no errors are produced but the results do not enter the table. Can anyone shed some light on this please, I've spent hours and hours and banging my head against a brick wall would probably be more constructive right now. Many thanks indeed for any help or advice. <?php mysql_connect("localhost", "$db_user","$db_upwd") or die ("Error - Could not connect: " . mysql_error()); mysql_select_db("$database"); $query="select host,count(*) from badc_mis_prog group by host"; $result = mysql_query($query) or die ("Error - Query: $query" . mysql_error()); $count=0; $hosts=array(); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { $html_hlname=$row[0]; $html_hlname=preg_replace("/</","<",$html_hlname); $html_hlname=preg_replace("/>/",">",$html_hlname); array_push($hosts, $html_hlname,$row[1],0); $count++; } $query="select host,count(*) from badc_mis_prog where reported=1 group by host"; $result = mysql_query($query) or die ("Error - Query: $query" . mysql_error()); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { $html_hlname=$row[0]; $html_hlname=preg_replace("/</","<",$html_hlname); $html_hlname=preg_replace("/>/",">",$html_hlname); for ($i=0; $i<($count*3); $i+=3) { if ($hosts[$i] == $html_hlname) { $hosts[($i+2)]=$row[1]; last; } } } for ($i=0 ; $i<(($count-1)*3); $i+=3){ for ($j=$i+3 ; $j<($count*3); $j+=3){ if ($hosts[($i+1)] < $hosts[($j+1)]){ $temp=array(); $temp[0]=$hosts[$i]; $temp[1]=$hosts[($i+1)]; $temp[2]=$hosts[($i+2)]; $hosts[$i]=$hosts[$j]; $hosts[($i+1)]=$hosts[($j+1)]; $hosts[($i+2)]=$hosts[($j+2)]; $hosts[$j]=$temp[0]; $hosts[($j+1)]=$temp[1]; $hosts[($j+2)]=$temp[2]; } } } print "<br><br><br><center><table border=\"1\">\n"; print "<tr><td>Host Name</td><td>Hosted</td><td>Reported</td><td>Ratio H/R</td></tr>\n"; for ($i=0; $i<($count*3); $i+=3) { if ($hosts[($i+1)]<15){ break;} printf ("<tr><td> %s </td><td> %d </td><td> %d </td><td>%.1f %%</td></tr>\n",$hosts[$i],$hosts[($i+1)],$hosts[($i+2)],(($hosts[($i+2)]/$hosts[($i+1)])*100)); } print "</table></center>\n"; ?> Hi I have the following code: Code: [Select] $result = mysql_query("SELECT * FROM xbox_games order by gameid"); $row = mysql_fetch_assoc($result); $id = $row["gameid"]; $title = $row["gametitle"]; $cover = $row["cover"]; <?php echo $title;?> Which displays only the first result from my database. How can i change this to display all the results either as a list, or in a table? Thanks My db is called localDB, and the table in that db is called MonthlySales. I want to display the table in a table in php. But to be able to filter the table by the column year, so if 2000 is selected only the values with 2000 are shown. Is there also a better way to populate the drop down menu? My script looks like: Code: [Select] <form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post' name='form_filter' > <select name="value"> <option value="all">All</option> <option value="2000">2000</option> <option value="2001">2001</option> </select> <input type='submit' value = 'Filter'> </form> <?php $link = mysql_connect('localhost', 'root', 'root'); if (!$link) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db('localDB', $link); if (!$db_selected) { die (mysql_error()); } // process form when posted if(isset($_POST['value'])) { if($_POST['value'] == '2000') { $query = "SELECT * FROM MonthlySales WHERE Year='2000'"; } elseif($_POST['value'] == '2001') { $query = "SELECT * FROM MonthlySales WHERE Year='2001'"; } else { $query = "SELECT * FROM MonthlySales"; } $sql = mysql_query($query); while ($row = mysql_fetch_array($query)) { $Id = $row["Id"]; $ProductCode = $row["ProductCode"]; $Month = $row["Month"]; $Year = $row["Year"]; $SalesVolume = $row["SalesVolume"]; echo "<tr>"; echo "<td>" . $row['Id'] . "</td>"; echo "<td>" . $row['ProductCode'] . "</td>"; echo "<td>" . $row['Month'] . "</td>"; echo "<td>" . $row['Year'] . "</td>"; echo "<td>" . $row['SalesVoulme'] . "</td>"; echo "</tr>"; } mysql_close($con); } ?> ---- I can not do this : $sql = mysql_query("UPDATE myMembers SET email_activated='1' WHERE id='$id' AND password='$hashpass'"); ---- i get this: $msgToUser = "<br /><br /><h3><strong><font color=red>Your account could not be activated!</font></strong><h3><br /> <br /> Please email site administrator and request manual activation. "; thanks guys ok so here is whats going on i have this small little php to update one thing in a table but it's not updating the mysql_error is You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'check=1 WHERE id='21'' at line 1 what is the prob because i spent a hour on this and cant git it? here is the php Code: [Select] <?php include '../config.php'; $id = $_GET['del']; mysql_query("UPDATE call_list SET check=1 WHERE id='$id'")or die(mysql_error()); header("Location: index.php"); ?> |
|||||||