Query was empty WHY!

Having problems with update statement and dropdown dialogue boxes

[darkenroyce]

Hi Guys

I'm new to PHP. I've been working on a site that allows users to pick a squad and monitor its progress.

My problems sound fairly similar to Yuppster's:

[webmasterworld.com...]

I'm running a update query which is supposed to enter some data for me via a drop dialogue box from a web page with a form. The code is as follows:

$sql="SELECT id FROM squad ORDER BY id DESC LIMIT 1";
$f=mysql_query($sql, $con) or die(mysql_error());
$a = mysql_fetch_assoc($f);
$row = $a++;

$sql_s="UPDATE squad SET play = '$_POST[sub]' WHERE us='$id' AND id='$row'";
mysql_query($sql_s, $con) or die(mysql_error());

The code for the drop down dialogue box is as follows:

<select name="sub">
<?php

$con = mysql_connect("localhost", "root") or die(mysql_error());

mysql_select_db("dbase", $con) or die(mysql_error());

$s = "SELECT id, name, value, position FROM players WHERE position='Q'";

$q = mysql_query($s, $con);

while($queryCol= mysql_fetch_array($q)){
echo '<option value="' . $queryCol ['id'] . '">' . $queryCol ['id'] . ' ' . $queryCol ['name'] . ' - ' . $queryCol ['player'] .'</option>';
}


?></select>

The drop dialogue box passes option value id to the update query which needs to populate where us=$id (the row's id & primary key) and to the next blank row after the previous row has been filled in the table hence $a++. It does not since MySQL table displays 0 when the id should be there. Is it because the fetch is incorrect? It is a two part registration system that requires the user id to ensure that the new user can register their details and then they choose players from the drop down box to place in the squad they intend to submit.

Please help! If I can get this part to work I reckon I have only small amounts to code.

Thanks,

Darkenroyce

[edited by: eelixduppy at 8:00 pm (utc) on April 15, 2008]
[edit reason] added code [/edit]

[d40sithui]

hmm, first thing i notice is

$row=$a++;

$a is an array, and it looks like you're incrementing the array variable, which doesn't do anything. are you trying to have $row equal to the array lenght + 1 or something else? please explain your objective more clearly.

[darkenroyce]

Hi,

With regards to $a I am trying to increment the value of id by one, not within the table but within the php script. Should I be using $a['id']?

Thanks for the reply,

darken

[eelixduppy]

>> Should I be using $a['id']?

Yes:

$row = $a['id']+1;

[darkenroyce]

Thanks eelixduppy.

I've changed the code to reflect what we discussed yesterday. Unfortunately, this has not changed the result significantly. Instead, the last row has the incorrect id associated to it. The other rows that precede still have no id. When removing "AND id='$row" the code does work but as I predicted the first ID is proliferated 15 times so the player will have 15 goalkeepers.

I echo the values and this displays the incorrect row numbers several times. Worst still, the $row for all player entries do not follow 2,3,4,5 and so on. Instead the echo displays 1,5,1,5,1,5,1,5,1,5,1,5,1,5,1. "Query is empty is still displayed also.

Any suggestions? If you wish to see the whole script I can private message it or email it.

[d40sithui]

im still a bit confused as to what you want to do. perhaps if you could explain your task in greater detail, we can better understand and better help you. if helpful, please provide a brief description of your table schema.

[darkenroyce]

Hi

Sure I have a word document which describes the problem slightly better

I have attached it below

Please reply if you guys need some more clarity.

Darkenroyce

[darkenroyce]

guys can i attach a word document on this thread?

[coopster]

No, you can cut/paste text from it though. Only the relevant parts though. Please refer to the forum Charter [webmasterworld.com] for details.