Jump to content

khefner

Members
  • Posts

    22
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

khefner's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. My script is working 80%, its this last part (UPDATE the database) that is proving difficult The overall idea is a form that fills data into the text boxes,(from the database) then, allows the user to UPDATE changes made in the input text boxes. First I connect to the database that provides a dropdown list for the user. The user selects a itemnumber from the drop down list, and after doing so, associated data from fields in the database populate the appropriate input boxes. That all works ok The submit button when pressed should UPDATE the new contents of the input text boxes into the database. The data is not updated into the database. I do not get a SQL error though. The data going in to the database should be associated with the variable itemnumber. Thanks, Kevin mysql_connect($hostname,$username, $password) OR DIE ('Unable to connect to database! Please try again later.'); mysql_select_db($dbname); echo "<h1 align='center'>SELECT PART</h1>"; $members_list = "<select name='name' onChange='this.form.submit();'>"; $members_list .= "<option selected></option>"; $query_members = "SELECT itemnumber,description FROM parts ORDER BY itemnumber"; $result_members = mysql_query($query_members) or die("QUERY MEMBERS FAILED: ".mysql_error()); while ($line = mysql_fetch_array($result_members)) { $itemnumber = $line['itemnumber']; $description = $line['description']; $members_list .= "<option value='$itemnumber'>$itemnumber ".ucwords($description)."</option>"; } $members_list .= "</select>"; if ($_REQUEST[name]) { $query_member_info = "SELECT * FROM parts WHERE itemnumber='$_REQUEST[name]'"; $result_member_info = mysql_query($query_member_info) or die("QUERY MEMBER INFO FAILED: ".mysql_error()); if (mysql_num_rows($result_member_info) == 1) { $description = ucwords(mysql_result($result_member_info, 0, 'description')); $itemnumber = mysql_result($result_member_info, 0, 'itemnumber'); $code = mysql_result($result_member_info, 0, 'code'); $timesinbom = mysql_result($result_member_info, 0, 'timesinbom'); $activepassive = mysql_result($result_member_info, 0, 'activepassive'); } echo "<form method='post' action='part_edit_4.php'>"; echo "<table border='0' width='100%' cellspacing='0' cellpadding='8'>"; echo " <tr>"; echo " <tr>"; echo " <td colspan='3'><table border='0' width='100%'>"; echo " <tr>"; echo " <td >Description</td>"; echo " <td > <input type = 'text' name= 'description' value ='$description'></td>"; echo " </tr>"; echo " <tr>"; echo " <td >Itemnumber</td>"; echo " <td > <input type = 'text' name= 'itemnumber' value ='$itemnumber'></td>"; echo " <tr>"; echo " <td >Code</td>"; echo " <td > <input type = 'text' name= 'code' value ='$code'></td>"; echo " </tr>"; echo " <tr>"; echo " <td >Times in BOM</td>"; echo " <td > <input type = 'text' name= 'timesinbom' value ='$timesinbom'></td>"; echo " </tr>"; echo " <tr>"; echo " <td >Active/Passive</td>"; echo " <td > <input type = 'text' name= 'activepassive' value ='$activepassive'></td>"; echo " </tr>"; echo " <tr>"; echo " <td > <input type = 'submit' name= 'submit' value ='Enter'></td>"; echo " </tr>"; echo " </table></td>"; echo "</form>"; if('submit'){ $result = "UPDATE parts SET description='$description',code='$code',timesinbom='$timesinbom',activepassive='$activepassive' WHERE itemnumber='$itemnumber'"; $result_update_last_report = mysql_query($result) or die("QUERY UPDATE LAST REPORT FAILED: ".mysql_error()); } } echo "<form method='post' action='part_edit_4.php'>"; echo "<table border='0' width='50%' cellspacing='0' cellpadding='8'>"; echo " <tr>"; echo " <td><table border='0' width='100%' cellspacing='0'>"; echo " <tr>"; echo " <td width='5%'> </td>"; echo " <td width='95%'><b>Select Part Number</b></td>"; echo " </tr>"; echo " <tr>"; echo " <td width='5%'> </td>"; echo " <td width='95%'> </td>"; echo " </tr>"; echo " <tr>"; echo " <td colspan='2'><table border='0' width='100%'>"; echo " <tr>"; echo " <td width='50%' align='right' >Part Number/Description: </td>"; echo " <td width='50%' bgcolor='#338899'>$members_list</td>"; echo " </tr>"; echo " </table></td>"; echo " </tr>"; echo " </table></td>"; echo " </tr>"; echo "</table>"; echo "</form>"; ?> </div> </body> </html>
  2. Thanks Barand that worked perfectly. SQL is amazing...!
  3. Ok here goes: I have a php page with Mysql that is using a left join. ( That works correctly.) $query_partdata = "SELECT electrical_BOM.itemnumber,electrical_BOM.description, electrical_BOM.code, parts.partnumber, parts.parttype, parts.package , parts.manufacturer, parts.screenlevel, parts.quallevel, parts.totaldoselevel, parts.seulevel, electrical_BOM.quantity, electrical_BOM.whereused, parts.parnumber From electrical_BOM LEFT JOIN parts ON electrical_BOM.itemnumber = parts.itemnumber"; Ok so I have two tables, electrical_BOM and parts Per the LEFT JOIN function all of the records from electrical_BOM are displayed after being joined with certain fields from parts.(This works ok, I output it to the screen) However I would like to somehow use a WHERE or other technique in which I only obtain the records in electrical_BOM when the field "code" (Which is in electrical_BOM) is equal to the number 2 or 3. I dont know how to use the LEFT JOIN in conjunction with the WHERE. Maybe there is a better way of doing this than using a LEFT JOIN? Thanks
  4. just wanted to say thanks to everyone for the help. I have the script working now. Regards, Kevin
  5. Thanks for the help regarding the "WHERE roll_frame='roll_frame'" What I am trying to do is take the value entered in the input box 'roll_frame', and use it to find a match in the database field called roll_frame.
  6. Well I used all the advice from the various posts as best I could and came up with this: (See below) The page displays, but when I enter a valid number and hit the submit button, the page refreshes but the database info is not there. I think one problem is that my if and else are not in the right locations? <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>PDF Retrieve Using Roll and Frame Number</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> <div align="center">PDF FINDER <br> <br> <?php $submit = $_REQUEST['roll_frame'] ; if(!isset($submit)){ //Connect To Database $hostname=''; $username='retrieve'; $password=''; $dbname='retrieve'; mysql_connect($hostname,$username, $password) OR DIE ('Unable to connect to database! Please try again later.'); mysql_select_db($dbname); $query_pdf = mysql_query ("SELECT pdf_link FROM data WHERE roll_frame ='roll_frame'") or die(mysql_error()); $info = mysql_fetch_array( $query_pdf ); Print "<b>pdf:</b> ".$info['pdf_link'] . " "; } else { echo "<form method='post'>"; echo " <b>Enter Roll and Frame number: </b><input type='text' name='roll_frame'><br><br>"; echo " <align='center'><input type='submit' value='retrieve pdf'><br><br>"; } ?> </div> </body> </html>
  7. I see now that I need to define the form - what would the action be? Thanks <form method='post' action='something here'>";
  8. Hello I am trying to do something pretty basic (I think!) but I am stuck on one part. Basically, I want to use a text input box to allow the user to enter a number. This number is then used to retrieve a record from the database. I have everything (connect to the database, manually retrieve some data etc) working except I dont know how to use the value from the input text box. I have a submit button but I dont know how to tie that in to the PHP. Here is the code: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Untitled Document</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> PDF FINDER <br><br> <?php echo " <b>Enter number: </b><input type='text' name='roll_frame'><br><br>"; echo " <align='center'><input type='submit' value='retrieve_pdf'><br><br>"; //Connect To Database $hostname=''; $username='retrieve'; $password=''; $dbname='retrieve'; mysql_connect($hostname,$username, $password) OR DIE ('Unable to connect to database! Please try again later.'); mysql_select_db($dbname); $query_pdf = mysql_query ("SELECT pdf_link FROM data WHERE roll_frame ='$roll_frame'") or die(mysql_error()); /*$query_pdf = mysql_query("SELECT * FROM data") or die(mysql_error()); */ $info = mysql_fetch_array( $query_pdf ); Print "<b>pdf:</b> ".$info['pdf_link'] . " "; /*Print "<b>roll frame:</b> ".$info['roll_frame'] . " <br>"; */ ?> </body> </html>
  9. I have a little mysql database that is taking in data from a php input form. What is happening is users are inputing data more than once, creating duplicate records in the database. I think its happening because they are getting impatient waiting for the "submission completed" page to display,and they are hitting the submit button a second time. What is the best way to eliminate duplicate entries from occuring? My second question is regarding the time it takes to update the database. It used to take about 10 seconds to update the data from my php form. (Its only text) It now takes about 30 to 45 sec. Could there be a issue with the database now that it has gotten a little bigger that is causing access to slow? It has about 175 records, all of which are text only. (about 12 fields per record) When I display the contents of the database on a webpage it updates in about 5 to 7 seconds, so it seems the slowdown occurs only when I write to the database. I recall that Microsoft Access had a database optimize function that reduced the size of the file. Perhaps I have to do something like that with the mysql database to speed it up? Thanks, Hef
  10. I have a little mysql database that is taking in data from a php input form. What is happening is users are inputing data more than once, creating duplicates. I think its happening because they are getting impatient waiting for the "submission completed" page to display,and they are hitting the submit button a second time. What is the best way to eliminate duplicate entries? My second question is regarding the time it takes to update the database. It used to take about 10 seconds to update the data from my php form. (Its only text) It now takes about 30 to 45 sec. Could there be a issue with the database now that it has gotten a little bigger that is causing access to slow? It has about 175 records, all of which are text only. (about 12 fields) When I display the contents ofthe database on a webpage it updates in about 5 to 7 seconds, so it seems the slowdown occurs only when I write to the database. I recall that Access had a database optimize function that reduced the size of the file. Perhaps I have to do something like that with mysql to speed it up? Thanks, Hef
  11. [!--quoteo(post=340909:date=Jan 29 2006, 01:47 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 29 2006, 01:47 PM) [snapback]340909[/snapback][/div][div class=\'quotemain\'][!--quotec--] First, it's UID, not VID -- which stands for unique identifier, which is usually the PRIMARY key. And FK stands for foreign key; it's what you use to establish relationships between tables. As for your concern about the same date, I meant to use a DATETIME field and insert NOW() into the record -- that would mean that you'd have it sorted to the second (at which point I don't think order matters at the second level!). It's not that using the UID field for this is wrong, it's just unwise -- for example, in InnoDB tables, the UIDs are reused if records are deleted, so your technique won't work anymore. The order displayed in PHPMyAdmin, or even the mysql command-line output, without an explicit sortorder is usually close to the order inserted, but it gets changed if any rows are deleted, because then the DB engine puts new rows "in between" in the empty spaces in the table file. That's why you can't rely on this order for anything. But as I said in a previous post, your script shouldn't care or rely on either the UID value or the native record order -- that's why you should use other fields to accomplish this task. Hope that helps. [/quote] Ahhhhhhh that makes a lot of sense! I will try the DATETIME format and sort by that. And you explained why my records were out of order. I had deleted some records, and thats where my order problems were located. Thanks for your help, I really appreciate it! Hef
  12. [!--quoteo(post=340720:date=Jan 28 2006, 06:51 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 28 2006, 06:51 PM) [snapback]340720[/snapback][/div][div class=\'quotemain\'][!--quotec--] I'm not sure what you mean by that... just do the following: [code]$result = mysql_query("SELECT * FROM warriors Order By userid ASC ", $db);[/code] BTW, why do you want to do this to begin with? The "proper" way is to store the create date, and sort by that -- you should never be using UIDs for anything other than FKs. [/quote] Ok I can sort by the create date thats fine. What do you mean by VID? (Primary key?) FK's? Basically, I have data coming in via the input form. Its important, that when its viewed via the html table, that it is displayed in order of when when they were inserted into the database. So I would agree that the date would work for that purpose, however what if you have multiple entries of the same date? If you sorted by date, you would not know which one was first, second, third etc. (of the same date) So, thats why I figured sorting by the "user id" which is a auto_incrementing field designated as the primary key, would make sense. By the way this is something that has me perplexed also. When I view the table data in phpmyadmin, some of the records are not "in order" . Another words, most of the records are sequential based on the primary key. However out of lets say 100 records there may be 3 or 4 that are out of order. This is the default view of the database table that I see with phpmyadmin without doing any sorting of any kind. When I use phpmyadmin to sort by userid, everything is in order. So I guess my question is, what is the default order of the records in a mysql database? Essentially this is why I need to sort - when users retrieve data from the database to a html table, some of the records are not in order of how they were received. Sorry for my ignorance - I know a little to be dangerous. But the more I do this stuff its kind of addicting and I really like it. Thanks, Hef
  13. Hello, I am trying to perform a basic sort by the primary key but I am having no success. Currently, I use PHP and SQL to pull data from the database to a html table for viewing - this works fine. But I want the data in the order that it is put into the database, so, I want to sort by the primary key. Here is what I tried to do, but it does not sort: mysql_query("SELECT * FROM warriors Order By userid ASC ", $db); $result = mysql_query("SELECT * FROM warriors", $db); I added the top line in an effort to sort, before the data is pulled. (userid is the name of the field that is the primary key) Thanks, Hef
  14. [!--quoteo(post=334643:date=Jan 8 2006, 07:39 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 8 2006, 07:39 PM) 334643[/snapback][/div][div class=\'quotemain\'][!--quotec--] There's nothing wrong with that default value, unless you want it to be NULL, in which case you have to change the column definition from NOT NULL. However, your SQL statement is incorrect -- you only pass "NOW()" as the value, and nothing else; the reason you're getting the default value is because what you've passed ('$date= NOW()' ) is just a string, which can't be in a date field. Try the following (UNTESTED): $sql = "INSERT INTO warriors (selectfootballcheer, participant, birthdate, school, PhoneNumber, Email, street, Town, Zip, Fathersname, Mothersname, textfield, date) VALUES ( '$selectfootballcheer', '$participant', '$birthdate', '$school', '$PhoneNumber', '$Email', '$street', '$Town', '$Zip', '$Fathersname', '$Mothersname', '$textfield', NOW() )"; Hope that helps. Thanks worked perfect!
  15. [!--quoteo(post=334413:date=Jan 7 2006, 04:52 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 7 2006, 04:52 PM) 334413[/snapback][/div][div class=\'quotemain\'][!--quotec--] The default value of a DATE field won't do what you want. You'll need to pass "NOW()" as the value for the DATE field in question whenever you issue your INSERT statement. In principle, you could use the TIMESTAMP column type as well, but IMHO, this is a very, very bad idea, and is more trouble than it's worth. Fenway, I added a field called "date" in the database with the type = "date". Below is my code from my form that I am using to pass the information to the database. When I submit, I dont get an error, but I am not updating the date field either with the current date. The date field has the default data - 0000-00-00 but not the current date. I dont feel confident my insert statement is correct in regards to the $date NOW(), so I think I have a problem there. When I made the date field type = date,(with PHPMYADMIN) it automatically made the default value = 0000-00-00 Is that ok? Thanks, Hef $sql = "INSERT INTO warriors (selectfootballcheer, participant, birthdate, school, PhoneNumber, Email, street, Town, Zip, Fathersname, Mothersname, textfield, date) VALUES ( '$selectfootballcheer', '$participant', '$birthdate', '$school', '$PhoneNumber', '$Email', '$street', '$Town', '$Zip', '$Fathersname', '$Mothersname', '$textfield', '$date= NOW()' )";
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.