[SOLVED] PHP / MySQL Help

[bigrossco]

I am trying to make a form to display results from a MySQL Database but dont know how to do it, what I want is the information from the database to be shown in the form so people can change any details on it i.e. a update form

I know how to put data in to the forum and update the databse, but would like the form to show all the user's information on the form sections i.e. Name, Address, Phone Number etc

Thanks in advance

Ross

[HuggieBear]

OK, a few simple questions first.

[list]
[*]Do you know how to create HTML forms with default values?
[*]Do you know how to retrieve an individual row from your database?
[/list]

Regards
Huggie

[bigrossco]

Thanks for getting back
[list]
[*]yes i know how to make forms with default values
[*]yes I know how to retreve rows from the database
[/list]

[HuggieBear]

OK, in that case, it should be as simple as this:

[code]<?php
// connect to db
include('connect.php');

// query for the record
$sql = "SELECT col1, col2, col3 FROM table WHERE condition = 'value'";
$result = mysql_query($sql);

// get the details into a single row
$row = mysql_fetch_array($result, MYSQL_ASSOC);

// echo the form
echo '<input type="text" name="column1" value="'. $row['col1'] .'"><br>\n';
echo '<input type="text" name="column2" value="'. $row['col2'] .'"><br>\n';
echo '<input type="text" name="column3" value="'. $row['col3'] .'"><br>\n';
?>[/code]

Regards
Huggie

[bigrossco]

I got the message : Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\CMS-Sys\update.php on line 88

and nothing displays just 3 empty box's

[HuggieBear]

In that case your sql statement is incorrect...

Change this:

[code=php:0]$result = mysql_query($sql);[/code]

To this:

[code=php:0]$result = mysql_query($sql) or die ("Can't execute $sql: " . mysql_error());[/code]

And then run it again.

Regards
Huggie

[bigrossco]

I get the message : Can't execute : Query was empty

this is my code:

  <?php


    $host = "$lang_dbhost";
    $user = "$lang_dbuser";
    $pass = "$lang_dbpass";
    $db = "$lang_dbase";

    // open connection
    $connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

    // select database
    mysql_select_db($db) or die ("Unable to select database!");

// query for the record
    $query = "SELECT * FROM clients WHERE id = '".$_SESSION["search"]."'";

$result = mysql_query($sql) or die ("Can't execute $sql: " . mysql_error());

// get the details into a single row
$row = mysql_fetch_array($result, MYSQL_ASSOC);

// echo the form
echo '<input type="text" name="column1" value="'. $row['col1'] .'"><br>';
echo '<input type="text" name="column2" value="'. $row['col2'] .'"><br>';
echo '<input type="text" name="column3" value="'. $row['col3'] .'"><br>';
?>

[HuggieBear]

You have the wrong variable name.  Change this...

[code=php:0]$result = mysql_query($sql) or die ("Can't execute $sql: " . mysql_error());[/code]

To this:

[code=php:0]$result = mysql_query($query) or die ("Can't execute $sql: " . mysql_error());[/code]

Also, you've left all my column names in, you need to change those to your column names.

Regards
Huggie

[bigrossco]

thanks working great now

I take it i will just need to add a submit button and the update query so people can change the info and update it?

[HuggieBear]

Yes, that's correct.

Don't forget to use the new 'Solved' button to mark this topic as solved.

Regards
Huggie

[bigrossco]

just before I solve it could you tell me the code for updating it, I know you can update records using something like:

[code]$query = "UPDATE INTO clients (fullname, date, location) VALUES ('$fullname', '$date', '$location') WHERE id = '".$_SESSION["search"]."'";[/code]

But not sure how to put it in to update it, I have the "submit" button added just unsure what to put after that.

Thanks again,

Ross

[HuggieBear]

Sure...

[code=php:0]UPDATE table_name SET col1 = 'value1', col2 = 'value2', col3 = 'value3' WHERE condition = 'something'[/code]

Regards
Huggie

[bigrossco]

thanks for that tooke me a few mins to figure out I needed to add some extra code to get it to work fully :) thanks for your help

Ross