Retrieving data to update

[joey111111]

Hi helper :)
Im trying do display all the users and there details so I can update them easier that going through each one individually but get this result "Unable to query the database" but dont know why....
Being at this for last few days and driving me insane.As much as an explanation as possible would be very most appriciated
joey111111

Fairly new to this so much help needed
Thanks again


<?

$id=$_GET['id'];//not sure if this needed

//connection here

$query="SELECT * FROM user WHERE user_id='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result) or die( "Unable to query the database" .mysql_error());

mysql_close();

$i=0;
while ($i < $num) {
$username=mysql_result($result,$i,"username");
$password=mysql_result($result,$i,"password");
$email=mysql_result($result,$i,"email");
$name=mysql_result($result,$i,"name");
$favclub=mysql_result($result,$i,"favclub");
$startdate=mysql_result($result,$i,"startdate");


?>

<form action="updated.php">
<input type="hidden" name="ud_id" value="<? echo "$id"; ?>">
Username <input type="text" name="ud_username" value="<? echo "$username"?>"><br>
Password: <input type="text" name="ud_password" value="<? echo "$password"?>"><br>
Email: <input type="text" name="ud_email" value="<? echo "$email"?>"><br>
Name: <input type="text" name="ud_name" value="<? echo "$name"?>"><br>
Favclub: <input type="text" name="ud_favclub" value="<? echo "$favclub"?>"><br>
Startdate: <input type="datetime" name="ud_startdate" value="<? echo "$startdate"?>"><br>

<input type="Submit" value="Update">
</form>

<?
++$i;
}
?>

[edited by: eelixduppy at 3:16 pm (utc) on Mar 10, 2010]
[edit reason] removed URL [/edit]

[Anyango]

Is the database connection getting established fine ? try

$result=mysql_query($query) or die(mysql_error());

Edit:
Also try the same mysql_error on mysql_connect and mysql_select_db.

That will tell you exactly where the issue is

[Matthew1980]

Hi there joey111111,

Welcome to the forum,

Firstly, echo the contents of the sql query to see if it being populated as expected.

Secondly, this won't make a difference to the execution of the script but will help the parser ;-p Make asure as you always use the full php tag: <?php as not all server configs allow short tags.

Thirdly, $query="SELECT * FROM user WHERE user_id='".$id."'"; you need to concatonate the sql query as so that the php value is added to the string.

Fourthly, bit dodgy having your username & password echod to the val part of the html tag too, especially as its plain text! Hopefully this data will only be viewable to you, at least have the data in sha or md5() encryption so that you can see the password ;-p

Cheers,

Good luck with your project,

MRb

[joey111111]

Get this error
Unknown column 'id' in 'where clause'

Dont really know what is means

thanks for fast responce

[Matthew1980]

Hi there Joey111111,

oops! Should have been:-

$query="SELECT * FROM `user` WHERE `user_id` = '".$id."'";

Call me pedantic, but I'm a stickler ;-p

Have you actually got a column in the DB with the heading of id, as this is what the error is, make sure that the name you asking for actually exists in the table ;-p

and I hadn't noticed this either before:-

$num = mysql_num_rows($result) or die( "Unable to query the database" .mysql_error());

You needed the underscore between num & rows!

Try that now!

Cheers,

MRb

[joey111111]

No getting this error now....Unable to query the database.....
I have a table called user and the user_id which is auto incremented...
This is code upto date with the replies receivied and am extremally greatfull for.

<?PHP

$id=$_GET['user_id'];
$conn = connection

mysql_select_db("mydb367", $conn)or die(mysql_error());

$query="SELECT * FROM `user` WHERE `user_id` = '".$id."'";
$result=mysql_query($query) or die(mysql_error()); ;
$num=mysql_num_rows($result) or die( "Unable to query the database" .mysql_error());
mysql_close();

$i=0;
while ($i < $num) {
$username=mysql_result($result,$i,"username");
$password=mysql_result($result,$i,"password");
$email=mysql_result($result,$i,"email");
$name=mysql_result($result,$i,"name");
$favclub=mysql_result($result,$i,"favclub");
$startdate=mysql_result($result,$i,"startdate");


?>

<form action="updated.php">
<input type="hidden" name="ud_id" value="<? echo "$id"; ?>">
Username <input type="text" name="ud_username" value="<? echo "$username"?>"><br>
Password: <input type="text" name="ud_password" value="<? echo "$password"?>"><br>
Email: <input type="text" name="ud_email" value="<? echo "$email"?>"><br>
Name: <input type="text" name="ud_name" value="<? echo "$name"?>"><br>
Favclub: <input type="text" name="ud_favclub" value="<? echo "$favclub"?>"><br>
Startdate: <input type="datetime" name="ud_startdate" value="<? echo "$startdate"?>"><br>

<input type="Submit" value="Update">
</form>

<?
++$i;
}
?>


Thanks again joey

[Matthew1980]

Hi there joey111111,

$id=$_GET['user_id'];
$conn = connection

Right, Joey, The $conn variable needs to have a value, not just an empty string to it ;-p

$conn is usually a variable name assigned to a function:-

$conn = mysql_connect("hostname", "Your Username","password") or die(mysql_error());

mysql_select_db("mydb367", $conn)or die(mysql_error());

$conn now has a function attached to it, and uses the username, password & hostname that you specified when you setup your database & tables therin

From now on the reference of "$conn" will now be associated with a mysql_query (I think subsequent ones are inherited from the first mysql_query(); that is made, though I might be wrong :/)

I'm not sure why you are setting a var and ++ (incrementing) it though, as the while could just have the $num query within, think of it as while this happens, I'll carry on and do this ;-p

Not meaning to appear rude in any way, but are you aiming for all the data from the id to be displayed in 'one shot', update & submit? If so I think I have read it right!

Though, I will make a suggestion as to how you are getting the data from the database:- (bear with me ;-p )

$query="SELECT * FROM `user` WHERE `user_id` = '".$id."'";
$result=mysql_query($query) or die(mysql_error());
//mysql_close(); this can be optional

if($result)
{

while ($num = mysql_fetch_object($result))
{
//getting info from DB, now process the form
?>

<form action="updated.php">
<input type="hidden" name="ud_id" value="<?php echo $id; ?>">
Username <input type="text" name="ud_username" value="<?php echo $num->$username; ?>"><br>
Password: <input type="text" name="ud_password" value="<?php echo $num->$password; ?>"><br>
Email: <input type="text" name="ud_email" value="<?php echo $num->$email; ?>"><br>
Name: <input type="text" name="ud_name" value="<?php echo $num->$name; ?>"><br>
Favclub: <input type="text" name="ud_favclub" value="<?php echo $num->$favclub; ?>"><br>
Startdate: <input type="datetime" name="ud_startdate" value="<?php echo $num->$startdate; ?>"><br>

<input type="Submit" value="Update">
</form>

<?php
}//close while
}//close if
else{//no data found/sent, throw clause to say!
echo "No data to get from the database!";
}
?>

Right, there is an alternative way for you to try if you can't get your own version going. Almost the same functionality, but I removed the Var with the ++ in as I couldn't see where it was being used.

Edit:
Doh! it's in the while, saying if(zero < rows returned) which pardon my maths, would always return false anyway? whereas it should be if(rows returned > zero) Please correct me if I read that wrong ;-p

I enclosed the while in an if, so that if there is no data from the query, don't build the form, at least this way you can tell if the table has any value or not. I personally think as this is good practise as then if anything goes wrong, you have the ability to handle an exception ;-)

Hope that helps a bit anyway.

Good luck ;-p

Cheers,

MRb

[joey111111]

Thats for your help Matthew
You explanation is very good but still not working sir.
No error on screen and no form displayed so I'm assuming that its not taking the information out of database to build the form(user table contains about 20 users) so def information in there.
Ill keep plugging away at it anyways and hopefully will figure it out..
Thanks again for your help and fast response!

[Matthew1980]

Hi there Joey111111,

Right then, <scratch of the head> lets see.

Put error_reporting(E_ALL); right at the top of the page(s) you are working on, this will then give any errors that could be to blame - I'm not saying as this will give a definitive answer but it's the best way to start.

The query.

After the query is assigned to the var, the next line, place echo $query; then directly under that place exit; What this will do is echo the query being sent, check to see if its being set properly, then (what my usual course of action is) copy and paste that code into the sql tab in phpmyadmin (I'm guessing as thats what your using) to execute that directly to the table, when you press go, if the query is formatted ok and there are values in there to be got, the query will return the results as your asking for. I assume as you are asking for one record at a time being as the where clause stipulates 1 id?

Placing the echo $query; & the exit; will need to be removed once you have confirmed that the query is being built correctly ;-p

Connecting.

Just to be safe, I would do this too, I should have done it first time round...

$result = mysql_query($query, $conn) or die(mysql_error());

Then at least there is a reference to the connection there.

Just a few idea's to start with, but it's a move in the right direction I think.

I just thought, how is the $_GET['id'] getting set, are you sure it has data (int) in it? Make sure that the $_GET['id'] what you expect in it...

Cheers,
MRb

[joey111111]

Hi Matthew
the query works fine...
put in the error_reporting(E_ALL); line and got this error only:
Notice: Undefined index: user_id in /home/danu2_oj1/public_html/FantasyProject/expupdate.php on line 3

Line 3 is $id=$_GET['user_id'];.....being changing it around but get same error.....and tried to google/bing also..

Also data has (int) in it
Really new to php and relly like but problems like this would really take the wind out of your sails.
Sorry for bothering you but your help is very much appricared
joey
Thanks again sir

[Readie]

I believe that, in this case, undefined index means that you don't have the URL as:

.../expupdate.php?user_id=12345

[rocknbil]

Precisely,

Notice: Undefined index: user_id in /always/anonymize/your/paths/in/posts/expupdate.php on line 3
Line 3 is $id=$_GET['user_id']

which is why you want to do

if (isset($_GET['user_id'])) {
$id=$_GET['user_id'];
}
else {
echo "user id is not set";
exit;
}

of course, sanitize it as suggested . . . if it's not set in get/post, there's no point in testing if $id is set.

[Matthew1980]

Hi there Joey111111,

Well, as I suggested, using the error_reporting(E_ALL); has basically given the answer to the question. Always a good idea to utilise this function when developing, but don't forget to remove/comment the code out when you come to release the software, as this could provide info for would be hackers.

If you (Joey111111) follow the $_GET['id']; and check that it is being set correctly - by checking the ?user_id= part of the URL to see if there is indeed a value being assigned there, that should then put you on the right heading to get the script functioning.

The code that Rocknbil suggests would be placed here:-

<?PHP
error_reporting(E_ALL); //Comment out once you release

if (isset($_GET['user_id'])) {
$id = strip_tags($_GET['user_id']);//cleanse the input & assign to var
}
else {
echo "user id is not set";
exit;
}

$conn = your connection detail here ;)

mysql_select_db("mydb367", $conn)or die(mysql_error());

$query = "SELECT * FROM `user` WHERE `user_id` = '".$id."'";
$result = mysql_query($query, $conn) or die(mysql_error()); ;
$num = mysql_num_rows($result) or die( "Unable to query the database" .mysql_error());

This will then basically say If $_GET['user_id'] exists, assign its value to $id, however, if it doesn't exist, echo a message to screen "User id is not set", and the exit; kills the script.

Hopefully now, that will solve the issue,

Sometimes things like this make us more aware of coding practices, and makes you realise that there are more than a couple of different ways to code certain things, as you progress you will develop your own style, this is just one of many learning curves to come ;-p But the more you solve the better as you will be for it. And, we all have to start somewhere, we were all beginners once.

Also FYI: (int) means integer, ie numerical, placing (int) in front of the (int)$_GET['id']; should force the value to be a whole number, *Please correct me if I am wrong* This is how I understand this to work..

BTW: Thanks to Readie & rocknbil too!

Good luck.

Cheers,
MRb

[joey111111]

Last question for me just to move away from this script...
How do I set the URL so it becomes as above

./expupdate.php?user_id=12345

Inserted the code that matthrew1980 and gives the error "User id is not set"

Sorry if being annoying but Im banging my head against a wall here.....

Thanks for all the help people,would like to buy yee all a pint....
p.s the explanations are very good...just new to php

[Readie]

./expupdate.php?user_id=12345

Is the URL you are visiting. You aquire the 12345 with $_GET['user_id']

And don't worry about it, we were all beginners too at some point :)

[Matthew1980]

HI there Joey111111,

Right then, that proves as there isn't a form that generates the URL.

You need to have another form before this one that the original post was about, from there the URL will be created. Unless you already have this.

Basically from what I can work out, the first form would do a db query to say something like:-

$Query_user_id = "SELECT `user_id` FROM `user`";

This will then Select ALL the user id's available in the database table `user`.

You then need to iterate through the results returned and then assign the text string of user id to a href to create the link, this is where the link is created.

As the action on this form will be the php file you first posted about with the exception that when the link is clicked the user_id will be attached to the link ie: put in the URL, this is then where your form code will work and populate the textboxes with the values from the database that are associated with the user_id that has been taken ($_GET) from the URL!

Sorry if that sounds confusing, but I have tried to explain it as best as I can.

OK, i have written this for you, I say now that I typed this on the fly so hopefully it will work ;-)

call this whatever file whatever you like so long as the action in the form is the name of the first file you posted with.

<body>

<?php
$sql_user_id = "SELECT `user_id` FROM `user`";
$sql_send = mysql_query($sql_user_id, $conn) or die(mysql_error());

if ($sql_send)
{//do the fetch data loop
?>
<form action="expupdate.php" method="post">
<?php
while($got = mysql_fetch_object($mysql_send))
{
//build form
?>
<p><a href="expupdate.php?user_id=<?php echo $got->user_id?>">User id <?php echo $got->user_id;?></p>
<?php
}//close while loop

}
else{
echo "No data to get";
}

?>
</form>
</body>
</html>

What this will do is query the DB for all of the user id's then the data it gets back will then be inserted into a <a href> so that the link is created. Once you fire it up you will see what I mean. From what you said, you have 20 users in the table so you should get 20 p tags with 20 links in each with DIFFERENT id numbers in.

When you click on the link the original file will be called, and as the $_GET['id'] is set, the rest of your original code will be actioned and everything should be ok *I hope!*

Once this has been proven to work you can then cosmetically change it, lets just get this going first.

*AGAIN* this was written on the fly, there may be a typo, but I have checked it over. Make sure that you put this in a new php file, from this point on you should be able to get a better idea of what is going on.

Good luck,

For anyone else reading this post, please point out any errors that there may be ;-p

EDIT: Doh! Just realised, you more than likely don't need the <form> tag in the code I just posted as the link carries all the info, it will work though, as there is no submit button to action the form!

See I kinda know what I'm doing and I still make errors!

Cheers,

MRb

[joey111111]

Matthew your a legend sir....
There is a small error in script in the while loop doth...
Think it should be ($sql_send)).
All user_id's are showing as you explained and when clicked on a id it prints out only one word-Username:(first word in the form) and the rest of page blank.
Not to worry Getting places now so Ill play around with the form and see what I can come up with
Thanks once again for your help!
Extremely greatful

[Matthew1980]

Hi there Joey111111,

Well we all have to start somewhere! Don't get to disheartened when things don't work first time, part of the fun is working out why, then the sense of accomplishment when you figure out and understand why it wasn't functioning. I'm struggling myself atm with a function I'm writing, but I'm nearly there!

As for the typo: I was quite tired last night! Your right though, it should read ($sql_send) that is the reference to the query, and the funtion its in then asks for the info from the database, whilst the page is created.

As for the semi-blank page, go through the debugging procedure again, echo the $sql queries to screen & make sure that the $_GET is being set and holds the value you are setting from the other file with the list of id numbers on it, IE: trace the request.

The building blocks are there now, it's just a matter of getting the data into the <inputs> Make sure that the file that is referenced in the links from the id list matches the php file you first posted from too, there may be a typo.

Also, on the <form> tag in your original post, you will need the attribute of method="post" ie: <form action="updated.php" method="post"> This will just tell the browser that the method of sending data is POST.

The page should be displaying a textbox there at least. Nevertheless, keep at it. ;-p

Good luck,

Cheers,

MRb

[joey111111]

Thanks very much for all your help...up and running now and doing what I want it to do...
Back to enjoying php haha

[Matthew1980]

Hi there Joey111111,

What was the cause of the semi-blank page then in the end?

Glad you got it sorted in the end though.

Have fun with the rest of the project.

Cheers,
MRb

[joey111111]

hey matt

Is was because I didnt have the variables declared....
All fixed now and project looking good
Thanks