Loading data from MySQL table, by a value found in database.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
TAViX
Forum Newbie
Posts: 13
Joined: Thu Feb 12, 2009 6:35 pm

Loading data from MySQL table, by a value found in database.

Post by TAViX »

Sorry guys, is the enoying me again. I have a very simple problem this time. But because is so simple I cannot make it work at all:crying: :crying: :crying:

Anyways, I have a table in MySQL, with something like this:

pr_id | entry | val1 | val2 | val3 | val4
00001 | 123 | bla1 | bla2 | bla2 |
00002 | 310 |
00003 | 234 |
00004 | 323 |
... ......

I created a text box for the entry values, so when I'm choosing a number from entry (ex: 123) all the datas from the 123 row (val1, val2, etc) to be listed for edit into a table. That's it.

Here is a sketch:
Image

Thank you very much in advance.
vmmene
Forum Newbie
Posts: 8
Joined: Mon Feb 09, 2009 3:26 am

Re: Loading data from MySQL table, by a value found in database.

Post by vmmene »

pr_id is your primary key?... what is the datatype of pr_id field??
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

Re: Loading data from MySQL table, by a value found in database.

Post by mattpointblank »

How about to make it easier, don't make them type the number first - show them it in a list. Something like this:

Code: Select all

 
<?php
// run a query and list all of your items
$query = "SELECT pr_id, entry FROM yourtablename ORDER BY entry";
$result = mysql_query($query) or die(mysql_error());
 
// now loop through and output edit links
while($row = mysql_fetch_array($result)) {
list($pr_id, $entry)  = $row;
    echo "$entry (<a href=\"?id=$pr_id\">edit</a>) <br/>";
}
 
// now edit a specific row if they request one
if(isset($_GET['id'])) {
   
    // run a query on the pr_id they selected
    $pr_id = mysql_real_escape_string($_GET['id']); // make sure you clean up user input like this
    $query = "SELECT val1, val2, val3 FROM yourtablename WHERE pr_id = '$pr_id'";
    $result = mysql_query($result) or die(mysql_error());
    $row = mysql_fetch_row($result);
 
    // now use the results from the query to populate a form, eg:
    $val1 = $row[0];
    $val2 = $row[1];  
    $val3 = $row[2];
 
    echo "<input name=\"val1\" type=\"text\" value=\"$val1\" />";
   // etc
 
 
}
 
 
?>
 
This isn't complete and will need some work from you, but it should give you the basics.
User avatar
TAViX
Forum Newbie
Posts: 13
Joined: Thu Feb 12, 2009 6:35 pm

Re: Loading data from MySQL table, by a value found in database.

Post by TAViX »

Thank you very much. :bow:
I allready know how to list the values, no problem here, but I need that specific option. WHEN I choose a value from that column, the data is dysplyed in the table, and I'm able to edit it again... :teach:
vmmene wrote:pr_id is your primary key?... what is the datatype of pr_id field??
YES, pr_id is the primary key, it's INT, NOT NULL,; I don't need to edit that is just for a reference in the db.
User avatar
susrisha
Forum Contributor
Posts: 439
Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India

Re: Loading data from MySQL table, by a value found in database.

Post by susrisha »

put name tags to the two buttons and in the submit page, check which one is defined. If the submit is got from the edit button, then update the values. if the submit is got from the show button, then edit the values in the text box accordingly.
User avatar
TAViX
Forum Newbie
Posts: 13
Joined: Thu Feb 12, 2009 6:35 pm

Re: Loading data from MySQL table, by a value found in database.

Post by TAViX »

susrisha wrote:put name tags to the two buttons and in the submit page, check which one is defined. If the submit is got from the edit button, then update the values. if the submit is got from the show button, then edit the values in the text box accordingly.
Yes, but how do I populate the textboxes with the curent values from the database row? I mean, I don't know the code to write...Yeah, I'm a genwine n00b.... :oops:
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

Re: Loading data from MySQL table, by a value found in database.

Post by mattpointblank »

TAViX wrote:
susrisha wrote:put name tags to the two buttons and in the submit page, check which one is defined. If the submit is got from the edit button, then update the values. if the submit is got from the show button, then edit the values in the text box accordingly.
Yes, but how do I populate the textboxes with the curent values from the database row? I mean, I don't know the code to write...Yeah, I'm a genwine n00b.... :oops:
Look at the code I posted for you. It does exactly this.
User avatar
TAViX
Forum Newbie
Posts: 13
Joined: Thu Feb 12, 2009 6:35 pm

Re: Loading data from MySQL table, by a value found in database.

Post by TAViX »

Ok here is what I did so far. A kick in a but here would be ok:

Code: Select all

 
<html>
 
<head>
 
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
 
<title>TEST</title>
 
<style type="text/css">
<!--
#layer1 {
    position:absolute;
    left:45px;
    top:27px;
    width:443px;
    height:247px;
    z-index:1;
}
-->
</style>
</head>
 
<body>
<div id="layer1">
 
<?php
$host = "localhost";
$user = "root";
$password = "";
$db = "db_rev00";
$table = "comp_info";
$con = mysql_connect($host,$user,$password);
mysql_select_db($db);
?>
 
<?php
 
// the values from the mysql database are: comp_id (primary key, int), comp_name, comp_abbr_name, comp_address, comp_tel, comp_fax, homepage_url, products, employee_num, comp_accept, comp_max_accept
 
// $query = "????";
 
$result = mysql_query($query) or die(mysql_error());
?>
 
 
 
<form action="" method="post" name="load_value" id="load_value">
    
    <table width="272" border="1" cellspacing="1" cellpadding="2">
      <tr>
        <th width="115" scope="row">Comp Code</th>
        <td width="86" align="center"><input name="insert_comp_code" type="text" id="insert_comp_code" size="10"></td>
        <td width="47"><input type="submit" name="Submit1" id="Submit1" value="Load"></td>
      </tr>
    </table>
    
    
    <p>
       <input name="comp_code" type="hidden" id="comp_code" value="
      <?php 
      echo $_POST['comp_code']; //hidden form to store the number imputed in the texbox 
      ?>">
    
 
    
</form>
 
 
 
 
 
<form action="" method="post" name="Post_values" id="Post_values">
 
<table width="346" border="1" cellspacing="1" cellpadding="2">
      <tr>
        <th width="157" align="left" scope="row">Comp. code</th>
        <td width="172" align="center"><input type="text" name="comp_code2" id="comp_code2">        </td>
      </tr>
      <tr>
        <th align="left" scope="row">Comp. name</th>
        <td align="center"><input type="text" name="comp_name" id="comp_name">        </td>
      </tr>
      <tr>
        <th align="left" scope="row">Comp. abbrev.</th>
        <td align="center"><input type="text" name="comp_abbr_name" id="comp_abbr_name">        </td>
      </tr>
      <tr>
        <th align="left" scope="row">Comp. address</th>
        <td align="center"><input type="text" name="comp_address" id="comp_address">        </td>
      </tr>
      <tr>
        <th align="left" scope="row">Comp. phone</th>
        <td align="center"><input type="text" name="comp_tel" id="comp_tel">        </td>
      </tr>
      <tr>
        <th align="left" scope="row">Comp. fax</th>
        <td align="center"><input type="text" name="comp_fax" id="comp_fax">        </td>
      </tr>
      <tr>
        <th align="left" scope="row">Comp. webpage</th>
        <td align="center"><input type="text" name="homepage_url" id="homepage_url">        </td>
      </tr>
      <tr>
        <th align="left" scope="row">Comp. products</th>
        <td align="center"><input type="text" name="products" id="products">        </td>
      </tr>
      <tr>
        <th align="left" scope="row">Empl. no.</th>
        <td align="center"><input type="text" name="employee_num" id="employee_num">        </td>
      </tr>
      <tr>
        <th align="left" scope="row">Comp. accept.</th>
        <td align="center"><input type="text" name="comp_accept" id="comp_accept">        </td>
      </tr>
      <tr>
        <th align="left" scope="row">Comp. max. acept.</th>
        <td align="center"><input type="text" name="comp_max_accept" id="comp_max_accept">        </td>
      </tr>
      <tr>
        <th align="left" scope="row">Comp. represent.</th>
        <td align="center"><input type="text" name="representative" id="representative">        </td>
      </tr>
      <tr>
        <th align="left" scope="row">Supervisor</th>
        <td align="center"><input type="text" name="supervisor" id="supervisor">        </td>
      </tr>
    </table>
    
      
             <p>
        <input type="submit" name="Submit2" id="Submit2" value="??">
        
    </p>
</form>
 
 
</div>
</body>
 
</html>
 
 
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

Re: Loading data from MySQL table, by a value found in database.

Post by mattpointblank »

You're almost there, but it feels like you're ignoring my code - look at how it's structured: I run a query (line 13 onwards), assign the results to variables, and then use those variables as the values for the input fields. That's all you need to do. It's no use asking for help if you won't listen when people give it.
User avatar
TAViX
Forum Newbie
Posts: 13
Joined: Thu Feb 12, 2009 6:35 pm

Re: Loading data from MySQL table, by a value found in database.

Post by TAViX »

Hey thanks alot!!!!. I allready managed to finish it. :irock::weee:

Here is the code now:

Code: Select all

 
<div id="the_code">
<?php
$host = "localhost";
$user = "root";
$password = "";
$db = "db_rev00";
$table = "comp_info";
$con = mysql_connect($host,$user,$password);
mysql_select_db($db);
mysql_query("SET NAMES 'utf8'");
if(!empty($_POST["update"]))
{
 mysql_query("UPDATE $table SET comp_id = '".mysql_real_escape_string($_POST["comp_id"])."', comp_code = '".mysql_real_escape_string($_POST["comp_code"])."', comp_name = '".mysql_real_escape_string($_POST["comp_name"])."', comp_abbr_name = '".mysql_real_escape_string($_POST["comp_abbr_name"])."', comp_address = '".mysql_real_escape_string($_POST["comp_address"])."', comp_tel = '".mysql_real_escape_string($_POST["comp_tel"])."', comp_fax = '".mysql_real_escape_string($_POST["comp_fax"])."', homepage_url = '".mysql_real_escape_string($_POST["homepage_url"])."', products = '".mysql_real_escape_string($_POST["products"])."', employee_num = '".($_POST["employee_num"])."', comp_accept = '".mysql_real_escape_string($_POST["comp_accept"])."', comp_max_accept = '".mysql_real_escape_string($_POST["comp_max_accept"])."', representative = '".mysql_real_escape_string($_POST["representative"])."', supervisor = '".mysql_real_escape_string($_POST["supervisor"])."' WHERE comp_code = '".mysql_real_escape_string($_POST["comp_code"])."'");
 echo "<b>Record successfully updated.</b>";
 
 }
$sw = 0;
$result = mysql_query("SELECT comp_code FROM $table");
echo "<form action='#' method='post'>
      <b>  ????  </b> <select name='comp_code'>";
While($row = mysql_fetch_assoc($result))
{
 if($sw == 0){$comp_code = $row["comp_code"];$sw = 1;}
 echo (!empty($_POST["comp_code"])&&$_POST["comp_code"]==$row["comp_code"])?"<option selected>".$row["comp_code"]."</option>":"<option>".$row["comp_code"]."</option>";
 }
echo "</select>
      <input type='submit' value='   SHOW   ' name='show'>
      </form>";
mysql_free_result($result);
$comp_code = empty($_POST["comp_code"])?$comp_code:$_POST["comp_code"];
echo "<form action='#' method='post'>
      <table width='306' border='1' cellpadding='2' cellspacing='1' bordercolor='#000000' id='tbl_upd'>";
$result = mysql_query("SELECT * FROM $table WHERE comp_code = '$comp_code'");
while($row = mysql_fetch_assoc($result))
{
   echo "<tr><td width='145' bgcolor='#2A00AA'><span class='style11'>?? ID (????????)</span></td>
    <td width='145' align='center'><input type='hidden' name='comp_id' value='".$row["comp_id"]."'>".$row["comp_id"]."</td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>?????</span></td>
    <td align='center'><input type='hidden' name='comp_code' value='".$row["comp_code"]."'>".$row["comp_code"]."</td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>??</span></td>
    <td align='center'><input type='text' name='comp_name' value='".$row["comp_name"]."'></td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>??</span></td>
    <td align='center'><input type='text' name='comp_abbr_name' value='".$row["comp_abbr_name"]."'></td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>??</span></td>
    <td align='center'><input type='text' name='comp_address' value='".$row["comp_address"]."'></td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>????</span></td>
    <td align='center'><input type='text' name='comp_tel' value='".$row["comp_tel"]."'></td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>FAX??</span></td>
    <td align='center'><input type='text' name='comp_fax' value='".$row["comp_fax"]."'></td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>??????</span></td>
    <td align='center'><input type='text' name='homepage_url' value='".$row["homepage_url"]."'></td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>????</span></td>
    <td align='center'><input type='text' name='products' value='".$row["products"]."'></td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>????</span></td>
    <td align='center'><input type='text' name='employee_num' value='".$row["employee_num"]."'></td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>?????????</span></td>
    <td align='center'><input type='text' name='comp_accept' value='".$row["comp_accept"]."'></td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>?????????</span></td>
    <td align='center'><input type='text' name='comp_max_accept' value='".$row["comp_max_accept"]."'></td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>????</span></td>
    <td align='center'><input type='text' name='representative' value='".$row["representative"]."'></td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>??????</span></td>
    <td align='center'><input type='text' name='supervisor' value='".$row["supervisor"]."'></td></tr>";
   }
  echo "</table>
  <p>
        <input type='submit' name='update' value='  UPDATE  '>
  </p>
        </form>";
  mysql_free_result($result);
 // }
// }
?>
</div>
 
 
Ok, so this one takes the values from the comp_code cell, and upload them into that table for edditing.

Now I have a little trouble adding another dropt down list to do the same thing, but instead on comp_code, I need another cell called comp_abbr_name. The diference is that this one is text not integer. So can you help me a little bitt implementing this to work on the same table??!? Thanks in advance.
Post Reply