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

TAVi80

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:

Thank you very much in advance.

djjjozsi

this is a simple question, but the answer could be difficult.

in your version, you designed that table with a limit,
and there is a difficult way to dinamically list them into textfields.

If you feel that a project is impossible, then you should re-design your table structure.

The simpliest normalisation is:
underlined fields are the Primary Keys.

table1
pr_id | entry

values_table
row_id | pr_id | value

TAVi80

Hmm....here is what i did so far. Those are the real values I'm going to use:

<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="&#26356;&#26032;">

</p>
</form>


</div>
</body>

</html>

So basicaly I have 2 forms. First I need to read the imput value (comp_code) from the first textbox, then to post the values in the second table in order to edit them...

djjjozsi

Whta you should do:

in the first form, you enter a code, then post it.
If the $_POST["comp_code"] has a value, select from the database their values, and build the 2th form.
In the second form you need to paste the hidden field to store the result's ID (primary key)

TAVi80

djjjozsi;10903673 wrote:
Whta you should do:

in the first form, you enter a code, then post it.
If the $_POST["comp_code"] has a value, select from the database their values, and build the 2th form.
In the second form you need to paste the hidden field to store the result's ID (primary key)

Yeah, the only problem is that writing code it's not my strong point...That's why I post here, I was hoping for some scripting help...⭕o:o

djjjozsi

You have the table, for the codes 🙂 i name it codes

if you posted the first form, you got a variable $_POST["comp_code"]

after the first form, lets create a query if comp_code has a value:

if(empty($_POST["comp_code"] ))
die("Comp code is missing, lets fill one");

 $query = sprintf("SELECT * FROM codes WHERE comp_id='%d'" ,  $_POST["comp_code"] );

run that query:

$result=mysql_query($query);

check if this result has found, if not stop the program with an error message...

if(mysql_num_rows($result)<1)
die("No companiy was found!");

if(mysql_num_rows($result)>1)
die("Too many companie found with the same ID, its not soo good!!!");

if its ok, then pull their fields

$r=mysql_fetch_object($result);

And we know that we expect only one row from the database, becouse of this fact you don't need to use the while loop to get the field's values.
This method is very good one, becouse the field names will be your new object's indexes,
such as:

$r->comp_name    ,   $r->comp_abbr_name  ,  $r->homepage_url

Then lets print the field's values, using a textfield, and don't forget to use the hidden field, where you will store the $r->comp_id

What's not clear till this point? If you have functions in this code, i suggest check them in the manual to understand the syntax, and feel free to ask for more.
http://php.net

this issue is not completed yet, but to continue you need to understand these few steps 🙂

TAVi80

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

Here is the code now:

<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>  &#20250;&#31038;&#30053;&#31216;  </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'>&#20250;&#31038; ID (&#65411;&#65438;&#65392;&#65408;&#65421;&#65438;&#65392;&#65405;)</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'>&#20250;&#31038;&#12467;&#12540;&#12489;</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'>&#21517;&#31216;</span></td>
    <td align='center'><input type='text' name='comp_name' value='".$row["comp_name"]."'></td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>&#30053;&#31216;</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'>&#20303;&#25152;</span></td>
    <td align='center'><input type='text' name='comp_address' value='".$row["comp_address"]."'></td></tr>
		 <tr><td bgcolor='#2A00AA'><span class='style11'>&#38651;&#35441;&#30058;&#21495;</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&#30058;&#21495;</span></td>
    <td align='center'><input type='text' name='comp_fax' value='".$row["comp_fax"]."'></td></tr>
		 <tr><td bgcolor='#2A00AA'><span class='style11'>&#12507;&#12540;&#12512;&#12506;&#12540;&#12472;</span></td>
    <td align='center'><input type='text' name='homepage_url' value='".$row["homepage_url"]."'></td></tr>
		 <tr><td bgcolor='#2A00AA'><span class='style11'>&#26989;&#21209;&#20869;&#23481;</span></td>
    <td align='center'><input type='text' name='products' value='".$row["products"]."'></td></tr>
		 <tr><td bgcolor='#2A00AA'><span class='style11'>&#24467;&#26989;&#21729;&#25968;</span></td>
    <td align='center'><input type='text' name='employee_num' value='".$row["employee_num"]."'></td></tr>
		 <tr><td bgcolor='#2A00AA'><span class='style11'>&#30740;&#20462;&#29983;&#21463;&#20837;&#21487;&#33021;&#20154;&#21729;</span></td>
    <td align='center'><input type='text' name='comp_accept' value='".$row["comp_accept"]."'></td></tr>
		 <tr><td bgcolor='#2A00AA'><span class='style11'>&#30740;&#20462;&#29983;&#21463;&#20837;&#26368;&#22823;&#20154;&#21729;</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'>&#25285;&#24403;&#26989;&#25512;</span></td>
    <td align='center'><input type='text' name='representative' value='".$row["representative"]."'></td></tr>
         <tr><td bgcolor='#2A00AA'><span class='style11'>&#25285;&#24403;&#30740;&#20462;&#25351;&#23566;</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.

djjjozsi

You can select from the table only one field and set that as UNIQUE.

DISTICT
SELECT DISTINCT field FROM table

and make a dropdown with these values. BUT! If you store categories/locations/ ect in the the same table, its a better way to store them into another table, this called DATABASE Normalization