i need some major mysql help

[mlppodge]

i have been trying this for quite some time now and i have come to the conclusion... i suck at mysql calls. so i was wondering if anyone would be willing to help me out on this. i have a database named "vartolova_site_vars" and a table named "user_settings". i need it to find the row where user = $username and then take post data and update the values. so the field "homepage" needs to be given the info from $_POST['homepage']

newho... any code will help 🙂 i will be reading it over and over trying to learn it.

[Jona]

[font=trebuchet ms]You can get started with something like the following.[/font]

<?php

$db = mysql_connect("host","username","password") or die("Could not connect to database: ". mysql_error());
 mysql_select_db("vartolova_site_vars") or die("Could not select database: ". mysql_error());

$username = $_POST["username"];

$query = "SET `homepage` = '".$_POST["homepage"]."' WHERE `user` = '$username'";
$result = mysql_query($query);

if(!$result){
  echo "Error: ". mysql_error();
}
?>

[drawmack]

First read this http://dev.mysql.com/doc/mysql/en/SELECT.html

Then read this http://dev.mysql.com/doc/mysql/en/UPDATE.html

then try this on for size:

<?php
$sql = "SELECT * FROM user_settings WHERE user=\"$username\"";
$row = mysql_fetch_assoc(mysql_query($sql));
$sql = "UPDATE user_settings SET \n"
foreach($row as $key => $val)
    $sql .= $key . '="' . $_POST[$key] . "\",\n";
$sql = trim($sql);
$sql = substr($sql,0,(strlen($sql)-1));
$sql .= "\nWHERE user=\"$username\"";
mysql_query($sql);
?>

[mlppodge]

<?php
session_start();

$db = mysql_connect("localhost","","") or die("Could not connect to database: ". mysql_error());
mysql_select_db("vartolova_site_vars") or die("Could not select database: ". mysql_error());

$sql = "SELECT * FROM user_settings WHERE user=\"$username\"";
$row = mysql_fetch_assoc(mysql_query($sql));
$sql = "UPDATE user_settings SET \n"
foreach($row as $key => $val)
$sql .= $key . '="' . $_POST[$key] . "\",\n";
$sql = trim($sql);
$sql = substr($sql,0,(strlen($sql)-1));
$sql .= "\nWHERE user=\"$username\"";
mysql_query($sql);
?>

ok, now i have this in the source. and now i uderstand the syntax but what i dont know is what the part of:

foreach($row as $key => $val)
$sql .= $key . '="' . $_POST[$key] . "\",\n";

and its givin this error:

Parse error: parse error, unexpected T_FOREACH in D:\Program Files\Apache Group\Apache2\htdocs\modify_settings.php on line 10

[drawmack]

put a semicolon at the end of the line before the foreach

[mlppodge]

lol just cant win

Warning: Invalid argument supplied for foreach() in D:\Program Files\Apache Group\Apache2\htdocs\modify_settings.php on line 10

that section now goes like:

$row = mysql_fetch_assoc(mysql_query($sql));
$sql = "UPDATE user_settings SET \n";
foreach($row as $key => $val)
$sql .= $key . '="' . $_POST[$key] . "\",\n";
$sql = trim($sql);
$sql = substr($sql,0,(strlen($sql)-1));
$sql .= "\nWHERE user=\"$username\"";
mysql_query($sql);

[drawmack]

use [php][/php] tags when posting code.

if(($query = mysql_query($sql)) === FALSE)
    die(mysql_errno() . ": " . mysql_error());

$row = mysql_fetch_assoc($sql); 
$sql = "UPDATE user_settings SET \n";
foreach($row as $key => $val) 
    $sql .= $key . '="' . $_POST[$key] . "\",\n"; 
$sql = trim($sql); 
$sql = substr($sql,0,(strlen($sql)-1)); 
$sql .= "\nWHERE user=\"$username\""; 
if(!mysql_query($sql))
        die(mysql_errno() . ": " . mysql_error());

that should shed some light on things

[mlppodge]

<?php 
session_start();

$db = mysql_connect("localhost","***","***") or die("Could not connect to database: ". mysql_error()); 
 mysql_select_db("vartolova_site_vars") or die("Could not select database: ". mysql_error());

$sql = "SELECT * FROM user_settings WHERE user=\"$username\""; 
if(($query = mysql_query($sql)) === FALSE) 
    die(mysql_errno() . ": " . mysql_error()); 

$row = mysql_fetch_assoc($sql);  

$sql = "UPDATE user_settings SET \n"; 
foreach($row as $key => $val)  

    $sql .= $key . '="' . $_POST[$key] . "\",\n";  

$sql = trim($sql);  

$sql = substr($sql,0,(strlen($sql)-1));  

$sql .= "\nWHERE user=\"$username\"";  

if(!mysql_query($sql)) 
        die(mysql_errno() . ": " . mysql_error());
?>

i think this is what you meant... but still gives me problems:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in D:\Program Files\Apache Group\Apache2\htdocs\modify_settings.php on line 11

Warning: Invalid argument supplied for foreach() in D:\Program Files\Apache Group\Apache2\htdocs\modify_settings.php on line 13
1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE user=""' at line 2

[drawmack]

Originally posted by mlppodge
1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE user=""' at line 2 [/B]

LOok at the mysql error $username isn't set so the query is malformed