Retrieve and update mysql data from .php file

Hi everyone,

I'm webmaster of a little Baseball team in Ghent (Belgium) and new to php language, I use to play with Actionscrip. I have a database (mysql) where all members info is stored.

I'm trying make a kind of 'editable' php FORM to retrieve mysql data and write it back to mysql. At this moment I've learn how to retrieve the data from mysql in a non-editable php file. BTW it fun to play with php script.

Retrieve data from database script in non-editable php file, this may help some newbees like me:

Connect to database code connect.php


<?php
DEFINE ('DB_USER', 'username');
DEFINE ('DB_PSWD', 'password');
DEFINE ('DB_HOST', 'localhost);
DEFINE ('DB_NAME', 'databaseName');
$condb = mysqli_connect(DB_HOST, DB_USER,DB_PSWD,DB_NAME);
?>

Retrive code search.php


<html>
<head>
<title>pageName</title>
<style type="text/css">
table {
background-color: #ADD8E6;
border: 1px solid black;
font-family: Arial; font-size: 14px;
}
th {
text-align: left;
}
</style>
</head>
<body>
<h1>MEMBERS SEARCH</h1>
<form method="post" action="search.php">
<input type="hidden" name="submitted" value="true"/>
<label> Search Category:
<select name="category">
<option value="first">First NAME</option>
<option value="last">Last NAME</option>
<option value="licencenr">Licence</option>
</select>
</label>
<label> Search Criteria:<input type="text" name="criteria" /></label>
<input type="submit" />
</form>
<?php
if (isset($_POST['submitted'])){
// connect to the DB
include('connect.php');
$category = $_POST['category'];
$criteria = $_POST['criteria'];
$query = "SELECT * FROM members WHERE $category LIKE '%".$criteria."%'";
$result = mysqli_query ($condb, $query) or die ('error getting data from database');
$num_rows = mysqli_num_rows ($result);
echo "$num_rows results found";
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
?>
<table>
 <tr>
 <td width="300" ><font face="Arial Black" size="2"><?php echo $row['last']?> <?php echo $row['first']?></font></td>
 </tr>
</table>
<table>
 <tr>
 <td width="100"><b>Licence #</b></td>
<td width="3">:</td>
 <td width="120"><?php echo $row['licencenr']?></td>
 </tr>
 <tr>
 <td width="100"><b>Birthday</b></td>
<td width="3">:</td>
 <td width="120"><?php echo $row['birth']?></td>
 </tr>
 <tr>
 <td width="100"><b>Age</b></td>
<td width="3">:</td>
 <td width="120"><?php echo $row['age']?></td>
 </tr>
 <td width="120"> </td>
 </tr>
</table>
<br>
<?php
}
}
?>
</body>
</html>

Searching in the internet I found this forum, I've also found a php script that might be what I'm looking for but I'm blocked, data is retrieved, the editable form shows up but don't get the data writed bask to my database. Get an ERROR: Error: 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 '' at line 3

Here the code:

RETRIEVE page: displayData.php


<form method="get"> 
<?php 
//connects to database 
$con = mysql_connect("localhost","username","password"); 
if (!$con) 
{ 
die('Could not connect: ' . mysql_error()); 
} 

mysql_select_db("databaseName", $con); 

//retrieve data from database 
$result = mysql_query("SELECT * FROM tableName"); 

Print "<table border cellpadding=3>"; 
while($row = mysql_fetch_array($result)) 
{ 
Print "<tr>"; 
Print "<td>" . $row['licencenr'] . "</td>"; 
Print "<td>" . $row['first'] . "</td>"; 
Print "<td>" . $row['mobile'] . "</td>"; 
Print "<td>" . $row['brith'] . "</td>"; 
Print "<td>" . "<a href='editData.php?action=edit&id=" . $row['licencenr'] . "'>Edit</a>" . "</td>"; 

Print "</tr>"; 
} 
echo "</table>"; 

?> 
</form>

Now the editData.php script:


<input type="hidden" name="ID" value="<? echo "$ID" ?>"> 

<html><head><title></title></head> 
<body> 
<?php 
$con = mysql_connect("localhost","username","password"); 
if (!$con) 
{ 
die('Could not connect: ' . mysql_error()); 
} 

mysql_select_db("databaseName", $con);

if (isset($_GET["abc"])) 
{ 
$ID=$_POST['licencenr']; 
$Name = $_POST['first']; 
$Telephone = $_POST['mobile']; 
$Birthday = $_POST['birth']; 

//Update database

$sql="UPDATE tableName 
SET Name='$Name', Telephone='$Telephone', Birthday='$Birthday' 
WHERE ID = $ID"; 
$result = mysql_db_query($db, $sql, $con); 

if (!mysql_query($sql,$con)) 
{ 
die('Error: ' . mysql_error()); 
} 
else { 
echo "Updated successfully!"; 
} 
//trying to mk the data appear into my textbox 
//mysql_close($con); 
} 
?> 

<?php 
$query = mysql_query ("SELECT * from members WHERE ID = $ID"); 
?> 

<table width="300" cellpadding="3" cellspacing="0" border="2"> 
<tr align="center" valign="top"> 
<td colspan="1" rowspan="1" align="center"> 
<form method="get"> 
<p> 
<input type="hidden" name="ID" value="<? echo $ID; ?>"> 
<p><label>Name: </label>&nbsp; 

<? echo "<input type=\"text\" name=\"Name\" value=\"$Name\">"; ?> 

</p> 
<p><label>Telephone: </label>&nbsp; 
<? echo "<input type=\"text\" name=\"Telephone\" value=\"$Telephone\">"; ?> 
</p> 
<label>Birthday: </label>&nbsp; 
<? echo "<input type=\"text\" name=\"Birthday\" value=\"$Birthday\">"; ?> 

<br> 
<br> 
<input name="abc" type="Submit" value="Update"> 
</p> 
</form></td></tr></table> 

</body> 
</html>

If someone could help me, it would be great!

Thx guys

[edited by: eelixduppy at 9:13 pm (utc) on Jan 30, 2012]
[edit reason] no personal URLs, please [/edit]

if (isset($_POST["abc"])) {
$ID=(isset($_POST['licencenr']))?$_POST['licencenr']:null;
$Name=(isset($_POST['Name']))?$_POST['Name']:null;
$Telephone=(isset($_POST['Telephone']))?$_POST['Telephone']:null;
$Birthday=(isset($_POST['Birthday']))?$_POST['Birthday']:null;
}

Retrieve and update mysql data from .php file

mysql php

fulanitok

rocknbil

fulanitok

fulanitok

fulanitok

fulanitok

g1smd

rocknbil

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week