Help Runnging Multiple Sql commands for Inventory Management

[smidgen11]

Hi,

 

Im creating a simple inventory management for IP phones. I have 1 database with 4 tables (phoneinventory, entering, exiting, available) all contain the same structure. I have a very simple form in which you can search for a users phone by username. After "SELECT * FROM $tbl_name WHERE UserName='$UserName'"; runs you can select the found user (pulled from the array) with a checkbox. The first query by UserName displays the correct results just doesnt run these next command when selected:

 

$sql1 = "DELETE FROM $tbl_name WHERE UserName='$del_id'";

$sql2 = "DELETE FROM $tbl2_name WHERE UserName='$del_id'";

$sql3 = "INSERT INTO $tbl3_name SELECT * FROM $tbl_name WHERE UserName='$del_id'";

$sql4 = "INSERT INTO $tbl4_name SELECT * FROM $tbl_name WHERE UserName='$del_id'";

 

My problem is that my script works fine if I do not filter results by username. It does not work when WHERE UserName='$UserName'"; is added to $sql. Here is most of my php file:

 

 

 

 

<?php

$host="localhost"; // Host name

$username="xxxx"; // Mysql username

$password="xxxx"; // Mysql password

$db_name="PhoneInventory"; // Database name

$tbl_name="phoneinventory"; // Table name

$tbl2_name="entering"; // Table name

$tbl4_name="exiting"; // Table name

$tbl3_name="available"; // Table name

 

// Connect to server and select databse.

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

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

 

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

$result=mysql_query($sql);

 

 

?>

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

<tr>

<td><form name="form1" method="post" action="">

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

<tr>

</tr>

<tr>

<td align="center" bgcolor="#FFFFFF" style="width: 43px">Select</td>

<td align="center" bgcolor="#FFFFFF"><strong>Location</strong></td>

<td align="center" bgcolor="#FFFFFF"><strong>FirstName</strong></td>

<td align="center" bgcolor="#FFFFFF"><strong>LastName</strong></td>

<td align="center" bgcolor="#FFFFFF"><strong>UserName</strong></td>

<td align="center" bgcolor="#FFFFFF"><strong>Extension</strong></td>

<td align="center" bgcolor="#FFFFFF"><strong>Mac</strong></td>

<td align="center" bgcolor="#FFFFFF"><strong>Type</strong></td>

</tr>

<?php

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

?>

<tr>

<td align="center" bgcolor="#FFFFFF" style="width: 43px; height: 27px;"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['UserName']; ?>"></td>

<td bgcolor="#FFFFFF" style="height: 27px"><? echo $rows['Location']; ?></td>

<td bgcolor="#FFFFFF" style="height: 27px"><? echo $rows['FirstName']; ?></td>

<td bgcolor="#FFFFFF" style="height: 27px"><? echo $rows['LastName']; ?></td>

<td bgcolor="#FFFFFF" style="height: 27px"><? echo $rows['UserName']; ?></td>

<td bgcolor="#FFFFFF" style="height: 27px"><? echo $rows['Extension']; ?></td>

<td bgcolor="#FFFFFF" style="height: 27px"><? echo $rows['Mac']; ?></td>

<td bgcolor="#FFFFFF" style="height: 27px"><? echo $rows['Type']; ?></td>

</tr>

<?php

}

?>

<tr>

<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>

</tr>

<?

// Check if delete button active, start this

if($delete){

for($i=0;$i<$count;$i++){

$del_id = $checkbox[$i];

 

$sql1 = "DELETE FROM $tbl_name WHERE UserName='$del_id'";

$sql2 = "DELETE FROM $tbl2_name WHERE UserName='$del_id'";

$sql3 = "INSERT INTO $tbl3_name SELECT * FROM $tbl_name WHERE UserName='$del_id'";

$sql4 = "INSERT INTO $tbl4_name SELECT * FROM $tbl_name WHERE UserName='$del_id'";

 

 

$result = mysql_query($sql1);

$result = mysql_query($sql2);

$result = mysql_query($sql3);

$result = mysql_query($sql4);

}

 

// if successful redirect to home.html

if($result){

echo "<meta http-equiv=\"refresh\" content=\"0;URL=home.html\">";

}

}

mysql_close();

?>

</table>

</form>

</td>

</tr>

</table>

 

</html>

 

 

 

 

Anyone have any ideas why it would work perfectly when using $sql="SELECT * FROM $tbl_name"; as my first query rather than being able to filter by UserName?

 

Thanks