fixing query

[mehranalmasi]

Hi,

I picked up some code from the internet to query my db and then update the records. The code has some errors but I like the solution so much that I want to try fixing it for use. When I run it on my db or the db provided by the code, I get the //link to update.... comment repeated as many times as the records exist and the table that follows the repeated comment is empty. Any and all help will be appreciated to fix the code.

 

thanks!

;

;

;

;

 

Set up database 

 

 

CREATE TABLE `test_mysql` (

`id` int(4) NOT NULL auto_increment,

`name` varchar(65) NOT NULL default '',

`lastname` varchar(65) NOT NULL default '',

`email` varchar(65) NOT NULL default '',

PRIMARY KEY (`id`)

) TYPE=MyISAM AUTO_INCREMENT=7 ;

 

--

-- Dumping data for table `test_mysql`

--

 

INSERT INTO `test_mysql` VALUES (1, 'Billly', 'Blueton', 'bb5@phpeasystep.com');

INSERT INTO `test_mysql` VALUES (2, 'Jame', 'Campbell', 'jame@somewhere.com');

INSERT INTO `test_mysql` VALUES (3, 'Mark', 'Jackson', 'mark@phpeasystep.com');

INSERT INTO `test_mysql` VALUES (4, 'Linda', 'Travor', 'lin65@phpeasystep.com');

INSERT INTO `test_mysql` VALUES (5, 'Joey', 'Ford', 'fordloi@somewhere.com');

INSERT INTO `test_mysql` VALUES (6, 'Sidney', 'Gibson', 'gibson@phpeasystep.com');

 

 

Create file - list_records.php 

 

 

############### Code

 

<?php

$host="localhost"; // Host name

$username=""; // Mysql username

$password=""; // Mysql password

$db_name="test"; // Database name

$tbl_name="test_mysql"; // Table name

 

// Connect to server and select database.

mysql_connect("$host", "$username", "$password")or die("cannot connect");

mysql_select_db("$db_name")or die("cannot select DB");

 

$sql="SELECT * FROM $tbl_name";

$result=mysql_query($sql);

?>

<table width="400" border="0" cellspacing="1" cellpadding="0">

<tr>

<td>

<table width="400" border="1" cellspacing="0" cellpadding="3">

<tr>

<td colspan="4"><strong>List data from mysql </strong> </td>

</tr>

 

<tr>

<td align="center"><strong>Name</strong></td>

<td align="center"><strong>Lastname</strong></td>

<td align="center"><strong>Email</strong></td>

<td align="center"><strong>Update</strong></td>

</tr>

<?php

while($rows=mysql_fetch_array($result)){

?>

<tr>

<td><? echo $rows['name']; ?></td>

<td><? echo $rows['lastname']; ?></td>

<td><? echo $rows['email']; ?></td>

 

// link to update.php and send value of id

<td align="center"><a href="update.php?id=<? echo $rows['id']; ?>">update</a></td>

</tr>

<?php

}

?>

</table>

</td>

</tr>

</table>

<?php

mysql_close();

?>

 

[gristoi]

remove "// link to update.php and send value of id"  from your script

[mehranalmasi]

thanks gristoi for your reply,

removing that text does not resolve the empty table  which is what I am trying to fix! No matter what I do, the records are not populating the table.

[gristoi]

do you just get a completely blank table?

[mehranalmasi]

I get the table column "List data from from mysql" and other "Name". "Lastname" "email" "Update" column headers under.  All is blank under the column headers except the update column which has the update link.

[gristoi]

try changing

while($rows=mysql_fetch_array($result)){

to

while($rows=mysql_fetch_assoc($result)){

 

also add

echo mysql_num_rows($result);

 

after your query to see if anything is actually being returned

 

[mehranalmasi]

changing to

while($rows=mysql_fetch_assoc($result)){

makes no difference in reslt.

 

adding

echo mysql_num_rows($result);

returns 6 six times which is the number of records in the table.

[gristoi]

change :

<td><? echo $rows['name']; ?></td>
<td><? echo $rows['lastname']; ?></td>
<td><? echo $rows['email']; ?></td>

// link to update.php and send value of id 
<td align="center"><a href="update.php?id=<? echo $rows['id']; ?>">update</a></td>

 

to

<td><?php echo $rows['name']; ?></td>
<td><?php echo $rows['lastname']; ?></td>
<td><?php echo $rows['email']; ?></td>


<td align="center"><a href="update.php?id=<?php echo $rows['id']; ?>">update</a></td>

[mehranalmasi]

Great! you made my day! your last suggestion fixed the problem. I will have to remember to use <?php at all times!! Thanks so much.

now when I get the update page and make a change in the record, I get a "Successful" result but the record is not changed. I have the code of update.php and update_ac.php below. Please let me know if I should close this case and open a new one. thanks again for your help.

 

------------------------------------------------------------------

update.php

 

<?php

$host="localhost"; // Host name

$username=""; // Mysql username

$password=""; // Mysql password

$db_name="test"; // Database name

$tbl_name="test_mysql"; // Table name

 

// Connect to server and select database.

mysql_connect("$host", "$username", "$password")or die("cannot connect");

mysql_select_db("$db_name")or die("cannot select DB");

 

// get value of id that sent from address bar

$id=$_GET['id'];

 

 

// Retrieve data from database

$sql="SELECT * FROM $tbl_name WHERE id='$id'";

$result=mysql_query($sql);

 

$rows=mysql_fetch_array($result);

?>

<table width="400" border="0" cellspacing="1" cellpadding="0">

<tr>

<form name="form1" method="post" action="update_ac.php">

<td>

<table width="100%" border="0" cellspacing="1" cellpadding="0">

<tr>

<td> </td>

<td colspan="3"><strong>Update data in mysql</strong> </td>

</tr>

<tr>

<td align="center"> </td>

<td align="center"> </td>

<td align="center"> </td>

<td align="center"> </td>

</tr>

<tr>

<td align="center"> </td>

<td align="center"><strong>Name</strong></td>

<td align="center"><strong>Lastname</strong></td>

<td align="center"><strong>Email</strong></td>

</tr>

<tr>

<td> </td>

<td align="center"><input name="name" type="text" id="name" value="<? echo $rows['name']; ?>"></td>

<td align="center"><input name="lastname" type="text" id="lastname" value="<? echo $rows['lastname']; ?>" size="15"></td>

<td><input name="email" type="text" id="email" value="<? echo $rows['email']; ?>" size="15"></td>

</tr>

<tr>

<td> </td>

<td><input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>"></td>

<td align="center"><input type="submit" name="Submit" value="Submit"></td>

<td> </td>

</tr>

</table>

</td>

</form>

</tr>

</table>

 

<?

 

// close connection

mysql_close();

 

?>

 

 

 

 

------------------------------------------------------------------------------

 

update_ac.php

 

<?php

$host="localhost"; // Host name

$username=""; // Mysql username

$password=""; // Mysql password

$db_name="test"; // Database name

$tbl_name="test_mysql"; // Table name

 

// Connect to server and select database.

mysql_connect("$host", "$username", "$password")or die("cannot connect");

mysql_select_db("$db_name")or die("cannot select DB");

 

// update data in mysql database

$sql="UPDATE $tbl_name SET name='$name', lastname='$lastname', email='$email' WHERE id='$id'";

$result=mysql_query($sql);

 

// if successfully updated.

if($result){

echo "Successful";

echo "<BR>";

echo "<a href='list_records.php'>View result</a>";

}

 

else {

echo "ERROR";

}

 

?>