[SOLVED] PHP/MySQL confusion

[twitch987]

Hi Guys.. new to the forum, so im not sure how tolerant you guys are with newbies i just started reading up on PHP/MySQL today as i need to use it for adding, updating/removing and viewing a database full of info about property rentals.. anyways, i 've got the database and the table created, and managed to add entries using PHP, and eventually got it to display on a page. Im now working on updating the fields but for some reason it wont work. I've got myself in a twist now, im not sure where i went wrong. heres my code, please point out anything wrong with it

 

The page where you make the changes:

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM properties WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result); 
mysql_close();

$i=0;
while ($i < $num) {
$address=mysql_result($result,$i,"address");
$bond=mysql_result($result,$i,"bond");
$description=mysql_result($result,$i,"description");
$propertynear=mysql_result($result,$i,"propertynear");
$rent=mysql_result($result,$i,"rent");
$ref=mysql_result($result,$i,"ref");

?>

<form action="updated.php">
<input type="hidden" name="ud_id" value="<? echo "$id" ?>">
Address: <input type="text" name="ud_address" value="<? echo "$address" ?>"><br>
Bond(£): <input type="text" name="ud_bond" value="<? echo "$bond" ?>"><br>
Property Near: <input type="text" name="ud_propertynear" value="<? echo "$propertynear" ?>"><br>
Rent(£): <input type="text" name="ud_rent" value="<? echo "$rent" ?>"><br>
Reference #: <input type="text" name="ud_ref" value="<? echo "$ref" ?>"><br>
<input type="Submit" value="Update">
</form>

<?
++$i;
} 
?>

 

the php script which is meant to process the changes:

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$ud_address=$_POST['address'];
$ud_bond=$_POST['bond'];
$ud_description=$_POST['description'];
$ud_propertynear=$_POST['propertynear'];
$ud_rent=$_POST['rent'];
$ud_ref=$_POST['ref'];
$query="UPDATE contacts SET address='$ud_address', bond='$ud_bond', description='$ud_description', propertynear='$ud_propertynear', rent='$ud_rent', ref='$ud_ref' WHERE id='$ud_id'";

mysql_query($query);
echo "Record Updated";
mysql_close();
?>

 

i've made lots of little changes and in honesty i've forgotten what i've changed at this point i've tried so many things.. any help would be great

[trq]

You haven't defined $ud_id anywhere within your updated.php php script. Fiz that and you should be good to go as fare as I can tell.

 

Word of advice though, don't use the short <? tag, use <?php always. The short tag is becoming depricated.

[twitch987]

hey - thanks for the reply.. i've added ud_id methinks i didnt quite do it right.. still no updates happening

 

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$ud_id=$_POST['id'];
$ud_address=$_POST['address'];
$ud_bond=$_POST['bond'];
$ud_description=$_POST['description'];
$ud_propertynear=$_POST['propertynear'];
$ud_rent=$_POST['rent'];
$ud_ref=$_POST['ref'];
$query="UPDATE contacts SET address='$ud_address', bond='$ud_bond', description='$ud_description', propertynear='$ud_propertynear', rent='$ud_rent', ref='$ud_ref' WHERE id='$ud_id'";

mysql_query($query);
echo "Record Updated";
mysql_close();
?>

 

thanks for the <?php tip i'll keep it in mind.. only just started using php so shouldnt be too hard kicking the <? habit!

[trq]

Change....

 

$ud_id=$_POST['id'];

 

to....

 

$ud_id=$_POST['ud_id'];

 

as defined in your form.

[ngreenwood6]

Another thing. Where does $id come from in the first page. I do not see where it is defined?

[twitch987]

I did as suggested and it seems im getting somewhere, i get some response now at least

Parse error: syntax error, unexpected T_STRING, expecting ']' in /home/toxxicst/public_html/sqltest/updated.php on line 12

 

Another thing. Where does $id come from in the first page. I do not see where it is defined?

fair question. in honesty i havent a clue, i've been getting myself in a twist i think i lost track rather badly.. "id" is the first field in the database table thats as far as my knowlege goes really

 

i zipped the whole thing here http://83.170.107.1/~toxxicst/sqltest/mess.zip if you want to have a look at the atrocity in all its glory

[ngreenwood6]

Back to my question because even being able to see your script doesn't help answer my question about where does $id come from. Is it passed to this page through the GET or POST? How did you define $id? If $id isn't defined on this page how will it know which $id to upate?

[twitch987]

$query="SELECT * FROM properties WHERE id='$id'";

 

does this define it? id is the first field in the table

 

please remember i only started readin up on php this mornin lol its only 1.58 pm

 

thanks alot for all ur help guys.. keep it comin

[ngreenwood6]

no that does not define the value of $id. You are trying to pull everything from the database where the id in the database matches $id but you did not set a value for $id.

 

An example of setting a value:

 

$ud_id=$_POST['id'];

 

That sets $ud_id the value of a id passed from another form. Does that make sense?

[twitch987]

yeah it does it makes perfect sense.. thanks man!

 

in the database the first field is "id" which is supposed to increase with each entry.. this is apparently being entered but nothing is increasing as far as i can see.

 

the page listing the entries was meant to have this $id , and it was supposed to be passed to the processing script to identify which entry to update. but none of this was happening.. i know where i went wrong!

 

not sure how the increasing id field works though.. the tutorial i was using tells me i need to enter it like this:

 

$query = "INSERT INTO properties VALUES ('','$address','$bond','$description','$propertynear','$rent','$ref')";

 

by leaving the first bit blank.. but that doesnt make sense to me since $id isnt mentioned anywhere, nor is it passed to the script from the form on the previous page.. How does this work?

 

 

[ngreenwood6]

That query is correct. You have the id in the database left empty because you probably have it set to autoincrement which automatically gives it a value. The problem is in your code. You need to have a section where it loops through the database and finds the entries and links them by their id. Then when you want to see a certain entry you have to make that entry a link(when you looped through the database) and have it pass the id to the next page. When the id is there you can pull the data from the form and display it. If I was you I would look into php's GET method. Google "php get" to find some examples. Play around with it abit until you find it. Then you will have to look into the WHILE clause for looping through the database. I will give you a basic example though.

 

$conn = mysql_connect("host", "username", "password");
mysql_select_db("database");
$query = "SELECT * FROM table";
$results = mysql_query($query);

//make your loop
while($row = mysql_fetch_array($resutls))
{
echo $row['id']; 
echo "<br>";
echo $row['name'];
}

 

Basically that is saying that your table has 2 values one is "id" and the other is "name". In the while loop it goes through all the entries and displays the id with a line break and then name. It will show all the results in the database.

[twitch987]

aaah i see.. you should write some tutorials man - you'd do a better job than most!

 

something else which has apparently been neglected in the tutorial, i wasnt told to set anything to auto_increment, yet when i try set the id field to auto_increment on PHPMyAdmin it spits this at me

 

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

 

naturally i havent a clue what this means, it may as well be in chinese

[ngreenwood6]

In every table that you have you can only have one field that auto_increments. Also, there is a primary key option when you set it up that you can set which is normally the id, you can probably change that to primary and then it will let you auto_increment. Every table that I create has a field named id that auto increments and is primary key. You can then use that id to link tables in the database or use it in your php scripts to find entries.

[twitch987]

aha! it works. you're a genius

 

i have another question but feel a bit guilty about asking you yet again lol.. feel free to ignore me

 

if i wanted my index page to return only results containing certain things in certain fields, how would this be achieved? my index currently contains the following

 

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM properties";
$result=mysql_query($query);

$num=mysql_numrows($result); 

mysql_close();

$i=0;
while ($i < $num) {
$address=mysql_result($result,$i,"address");
$bond=mysql_result($result,$i,"bond");
$description=mysql_result($result,$i,"description");
$propertynear=mysql_result($result,$i,"propertynear");
$rent=mysql_result($result,$i,"rent");
$ref=mysql_result($result,$i,"ref"); 
?>

<tr> 
<td><font face="Arial, Helvetica, sans-serif"><? echo "$address"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$bond"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$description"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$propertynear"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$rent"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$ref"; ?></font></td>
</tr>
<?
$i++;
} 
echo "</table>"
?>

 

after you've helped me so much i dont ecpect you to keep reeling off examples for me a tip for google keywords would be great though

 

thanks again

[ngreenwood6]

I have no problem helping you. I actually created something for you to check out. If you open the zip file attached you can see three files. copy the two .php files to a folder on your webserver open the index.php file and make sure you put your database settings with the exemption of the database as that is contained in the sql file. Then open up phpmyadmin and import the database I have created (its the .sql file). Then open the folder on your webserver and there is a good example of the loop and $_GET in there. If you actually open the files with a text editor I put some good details in there.

 

As far as only returning certain results you would have to use the mysql WHERE clause. you would change your $query to something like this:

 

$query="SELECT * FROM properties WHERE  bond < 10";

 

I have no idea what your bond is but its just an example you can use the < or > or = or >= or <= in the comparison. you can also use a LIKE clause if you need to search for a word or something. like this;

 

$query="SELECT * FROM properties WHERE address LIKE '%home%'";

 

That would find any address that has the word home in it. If you would like you can google mysql like or mysql where and get some results. I like the http://www.tizag.com site personally.

 

[attachment deleted by admin]

[twitch987]

brilliant! im hoping to use a few variables, is it possible to do this with something like && or am i getting other programming languages mixed in here for no reason?

 

something along the lines of

 

$query="SELECT * FROM properties WHERE address LIKE '%home%' && bond BETWEEN '$start_bond' AND '$end_bond'";

[ngreenwood6]

yes you can compare with multiple values but you need to use AND or OR when you compare and make sure that you are using them in the correct context.

[twitch987]

ok great.. i take it thats the wrong syntax and context though?

 

slight problem, not sure if the update thing works or not the php on my server is messed up lol..

 

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$ud_id=$_POST['ud_id'];
$ud_address=$_POST['ud_address'];
$ud_bond=$_POST['ud_bond'];
$ud_description=$_POST['ud_description'];
$ud_propertynear=$_POST['ud_propertynear'];
$ud_rent=$_POST['ud_rent'];
$ud_ref=$_POST['ud_ref'];
$query="UPDATE properties SET address='$ud_address', bond='$ud_bond', description='$ud_description', propertynear='$ud_propertynear', rent='$ud_rent', ref='$ud_ref' WHERE id='$ud_id'";

mysql_query($query) or die(mysql_error());
echo "Record Updated";
mysql_close();
?>

 

spot anything? i really wana get this over with and move on lol

[ngreenwood6]

I think you are missing some "". fix this

mysql_connect(localhost,$username,$password);

 

with this

mysql_connect("localhost",$username,$password) or die(mysql_error());

 

You should also always use the or die(mysql_error()) whenever you are testing to see if it throws an error. Also did you actually define the $username, $password, and $database somewhere else because they are not defined on this page.

 

edit: please let me know any errors that you are getting.

[twitch987]

cool thanks.. yeah they're in a seperate file

 

include("dbinfo.inc.php")

 

not sure why, apparently its a good idea though.. monkey see monkey do

 

im not getting any errors, yet things arent being updated (php is back on track according to uk2)

 

<?
include("dbinfo.inc.php");
mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die(mysql_error());
$query="SELECT * FROM properties";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$address=mysql_result($result,$i,"address");
$bond=mysql_result($result,$i,"bond");
$description=mysql_result($result,$i,"description");
$propertynear=mysql_result($result,$i,"propertynear");
$rent=mysql_result($result,$i,"rent");
$ref=mysql_result($result,$i,"ref");

?>

<form action="updated.php">
<input type="hidden" name="ud_id" value="<? echo "$id" ?>">
Address: <input type="text" name="ud_address" value="<? echo "$address" ?>"><br>
Bond(£): <input type="text" name="ud_bond" value="<? echo "$bond" ?>"><br>
Description: <input type="text" name="ud_description" value="<? echo "$description" ?>"><br>
Property Near: <input type="text" name="ud_propertynear" value="<? echo "$propertynear" ?>"><br>
Rent(£): <input type="text" name="ud_rent" value="<? echo "$rent" ?>"><br>
Reference #: <input type="text" name="ud_ref" value="<? echo "$ref" ?>"><br>
<input type="Submit" value="Update">
</form>

<?
++$i;
}
?>

 

<?
include("dbinfo.inc.php");
mysql_connect("localhost",$username,$password) or die(mysql_error());
@mysql_select_db($database) or die( "Unable to select database");
$ud_id=$_POST['ud_id'];
$ud_address=$_POST['ud_address'];
$ud_bond=$_POST['ud_bond'];
$ud_description=$_POST['ud_description'];
$ud_propertynear=$_POST['ud_propertynear'];
$ud_rent=$_POST['ud_rent'];
$ud_ref=$_POST['ud_ref'];
$query="UPDATE properties SET address='$ud_address', bond='$ud_bond', description='$ud_description', propertynear='$ud_propertynear', rent='$ud_rent', ref='$ud_ref' WHERE id='$ud_id'";

mysql_query($query) or die(mysql_error());
echo "Record Updated";
mysql_close();
?>

 

really sorry about this i know im being a pain

[ngreenwood6]

try these pages:

 

<?php
include("dbinfo.inc.php");
mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die(mysql_error());
$query="SELECT * FROM properties";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$address=mysql_result($result,$i,"address");
$bond=mysql_result($result,$i,"bond");
$description=mysql_result($result,$i,"description");
$propertynear=mysql_result($result,$i,"propertynear");
$rent=mysql_result($result,$i,"rent");
$ref=mysql_result($result,$i,"ref");

?>

<form method="post" action="updated.php">
<input type="hidden" name="ud_id" value="<?php echo $id; ?>">
Address: <input type="text" name="ud_address" value="<?php echo $address; ?>"><br>
Bond(£): <input type="text" name="ud_bond" value="<?php echo $bond; ?>"><br>
Description: <input type="text" name="ud_description" value="<?php echo $description; ?>"><br>
Property Near: <input type="text" name="ud_propertynear" value="<?php echo $propertynear; ?>"><br>
Rent(£): <input type="text" name="ud_rent" value="<?php echo $rent; ?>"><br>
Reference #: <input type="text" name="ud_ref" value="<?php echo $ref; ?>"><br>
<input type="Submit" value="Update">
</form>

<?php
$i++;
}
?>

 

and this:

<?php
include("dbinfo.inc.php");
mysql_connect("localhost",$username,$password) or die(mysql_error());
@mysql_select_db($database) or die( "Unable to select database");
$ud_id=$_POST['ud_id'];
$ud_address=$_POST['ud_address'];
$ud_bond=$_POST['ud_bond'];
$ud_description=$_POST['ud_description'];
$ud_propertynear=$_POST['ud_propertynear'];
$ud_rent=$_POST['ud_rent'];
$ud_ref=$_POST['ud_ref'];
$query="UPDATE properties SET address='$ud_address', bond='$ud_bond', description='$ud_description', propertynear='$ud_propertynear', rent='$ud_rent', ref='$ud_ref' WHERE id='$ud_id'";

mysql_query($query) or die(mysql_error());
echo "Record Updated";
mysql_close();
?>

 

you should always use <?php because <? (short_open_tags) is not supported by all webhosts.

[twitch987]

same result

[ngreenwood6]

Make sure your code is the same as mine because I edited it. Can I see this site?

[twitch987]

i copy/pasted..

 

http://83.170.107.1/~toxxicst/sqltest/index.php  -results list

http://83.170.107.1/~toxxicst/sqltest/update.php  -update page

[ngreenwood6]

oh ok I get it now lol. You are passing three id's to the page. You need to make it so that when you want to edit something the link passes the id to the update page. The update page shows the results in the text boxes and then when you click update it only updates that one link. Does that makes sense to you?

 

For example on your index page you could make "83 rookwood road" a link that carried the $id to the update page. Then on the update page it only showed that one update section for that specific $id. When you clicked update it would then update that result. If you want I could modify this for you to work if you zipped up the files and uploaded them.