登录 | 注册 | FAQ
Anonymous

Joins with Like Statement Retrieving from MYSQL

+ 发表回复

2 篇帖子 分页: 1 / 1


Joins with Like Statement Retrieving from MYSQL

yasmin » 周四 5月 29, 2014 6:49 am

I am working with joins in MYSQL database and fromt end PHP.
Now i have two tables. Table1 has id,description. Table2 has id,status,description,quantity. Table1 id has 11 digits number which is matched with 13 digits id in the table2. i want to retrive the data based on the first 11 digits matched in table1 & table2 into selection button,based on selection option from the database it will retrieve the table2 data.



index.php:
<html>
<head>
<script>
function showUser(str) {
if (str=="") {
document.getElementById("txtHint").innerHTML="";
return;
}
if (window.XMLHttpRequest) {
// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
} else { // code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4 && xmlhttp.status==200) {
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getuser.php?idn="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>


<?php
$conn = mysql_connect('localhost', 'root', '');
if (!$conn)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("test", $conn);
$Select = "SELECT idn,desv FROM state ";

$Q = mysql_query($Select);
?>
<form>
<center> <select onchange="showUser(this.value)"></center>
<option value="">Select a person:</option>

<?php while($row = mysql_fetch_assoc($Q))
{

echo " <option value="'$idn'.,.'$desv'" > $row[idn] , $row[desv]</option>";

}
?>
</select>
</form>
<div id="txtHint"></div>

</body>
</html>


getuser.php:

<?php
$idn =($_GET['idn']);

$con = mysqli_connect('localhost','root','','test');
if (!$con) {
die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"ajax_demo");
$sql="(SELECT * FROM state1 WHERE idn = '".$idn."')ORDER BY num DESC";
$result = mysqli_query($con,$sql);

echo "<table border='1'>
</br>
<tr>
<th>Edit</th>
<th>ID</th>
<th>num</th>
<th>des</th>
<th>qnty</th>
</tr>";

while($test = mysqli_fetch_array($result)) {

echo "<tr>";

$no = $test['num'];
echo "<tr align='center'>";
echo"<td> <a href ='view.php?num=$no'><img src='pencil.png' width='20' height='20'></a>";
//echo"<td> <a href ='del.php?num=$no'><img src='cross.jpg' width='20' height='20'></a>";
echo "<td>" . $test['idn'] . "</td>";
echo "<td>" . $test['num'] . "</td>";
echo "<td>" . $test['desv'] . "</td>";
echo "<td>" . $test['qnty'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

view.php:

<?php
//require("db.php");
$conn = mysql_connect('localhost', 'root', '');
if (!$conn)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("test", $conn);
$no =$_REQUEST['num'];

$result = mysql_query("SELECT * FROM state WHERE num = '$no'");
$test = mysql_fetch_array($result);
if (!$result)
{
die("Error: Data not found..");
}
$idn=$test['idn'] ;
$num=$test['num'] ;
$desv= $test['desv'] ;
$qnty= $test['qnty'] ;
if(isset($_POST['save']))
{
$idn_save = $_POST['idn'];
$num_save = $_POST['num'];
$desv_save = $_POST['desv'];
$qnty_save = $_POST['qnty'];


mysql_query("UPDATE state SET idn ='$idn_save',num ='$num_save', desv ='$desv_save',qnty ='$qnty_save' WHERE num = '$no'")
or die(mysql_error());
echo "Saved!";

//header("Location: index.php");
}
mysql_close($conn);
?>
<html>
<head>
</head>

<body>
<form method="post">
<table>
<tr>
<td>Id:</td>
<td><input type="text" name="idn" value="<?php echo $idn ?>"/></td>
</tr>
<tr>
<td>Number:</td>
<td><input type="text" name="num" value="<?php echo $num ?>"/></td>
</tr>
<tr>
<td>Description:</td>
<td><input type="text" name="desv" value="<?php echo $desv ?>"/></td>
</tr>
<tr>
<td>Quantity:</td>
<td><input type="text" name="qnty" value="<?php echo $qnty ?>"/></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input type="submit" name="save" value="save" /></td>
</tr>
</table>

</body>
</html>
头像

yasmin

  • 帖子: 2
  • 注册: 周四 5月 29, 2014 6:17 am

Re: Joins with Like Statement Retrieving from MYSQL

XainPro » 周五 5月 30, 2014 7:41 pm

i have tried your code but i can tell you the logic

1st retrieve the from table 1 by simple select statement

and ltrim the id value 2 charterers from right and use it in like statement
头像

XainPro

  • 帖子: 3933
  • 注册: 周五 2月 17, 2012 8:10 pm


+ 发表回复

分页: 1 / 1