runnerjp Posted May 18, 2008 Author Share Posted May 18, 2008 ok account field in tagboard table is what account the tags are assosiated with... so then i can do this <?php $SQL = "SELECT * FROM $tablname WHERE account = '$user2' ORDER BY id DESC LIMIT $howmany";?> only selecting the posts made uder the users profile ( $user2 = $_GET['username']; ) ok im trying to make it so now the image like u suggested is under users $query = "INSERT INTO `users` (`ext`) VALUES ('$file_ext') WHERE ID = '$id' ON DUPLICATE KEY UPDATE ext = '$file_ext'"; but i get 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 ID = '1' ON DUPLICATE KEY UPDATE ext = 'gif'' at line 1 Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 18, 2008 Share Posted May 18, 2008 I'm not even sure why you have a separate field for the extension - why not just store the image name and extension together? Run this query to add a new field to your users table: ALTER TABLE `users` ADD `image` VARCHAR( 255 ) NOT NULL Then run this query to make the image field equal to the user's id plus the relevant extension: UPDATE users SET image = CONCAT(ID,'.',(SELECT ext FROM user_images WHERE user_images.user_id=users.ID)) The above queries only need be run once. I should probably add that if you were wanting to let users add more than one image, you would be correct in thinking you need a second table. However, if you're just wanting to allow users to have an avatar (or similar) then you might as well stick with the one table. And lastly, im still unsure about this account field. Whats the difference between account and nick? Quote Link to comment Share on other sites More sharing options...
runnerjp Posted May 18, 2008 Author Share Posted May 18, 2008 ok so when i upload an image what would my query be ?? $query = "INSERT INTO `users` (`image`) VALUES ('$file_ext') ON DUPLICATE KEY UPDATE ext = '$file_ext'"; ok i will show you the difference by showing you the code .... $user = $_POST['username']; $id = $_SESSION['user_id']; $username = get_username($id); $SQL = "INSERT INTO $tablname (nick, url, account, message, datetime, ip) VALUES ('$username', 'http://www.r.com/members/$username', '$user', '$message', '" . time() . "', '$REMOTE_ADDR');"; so nick is the name of the person who posted the message e.g GingerRobot says hello and account is the profile that they are posting the message onto..so e.g GingerRobot says hello on runnerjp's profile runnerjp says hello to you to0 on GingerRobot's profile so then when you go on say GingerRobot's profile he will be able to see the message runnerjp says hello to you to0 because it was on his profile but not GingerRobot says hello on runnerjp's profile because it was not on his profile... does that make sence Quote Link to comment Share on other sites More sharing options...
runnerjp Posted May 18, 2008 Author Share Posted May 18, 2008 did it help atall? Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 18, 2008 Share Posted May 18, 2008 Yes, i understand what you mean about the account now. Are you now using the above suggestion for storing the image? If so, the following should work for you: <?php $sql = "SELECT t.* ,u.image FROM `tagboard` AS t, users AS u WHERE t.account = '$user2' AND t.nick = u.Username ORDER BY t.id DESC LIMIT 10"; $result = mysql_db_query($database,$sql,$connection) or die(mysql_error().'<br />Query was:'.$sql); while($row = mysql_fetch_assoc($result)){ $nick = $row["nick"]; $url = $row["url"]; $message = $row["message"]; $datetime = $row["datetime"]; $senton= date("M jS, Y \a\\t h:i A T", $datetime); $img = $row['image']; //echo out as required echo 'Username:'.$nick.'<br />'; echo 'URL:'.$urk.'<br />'; echo 'Message:'.$message.'<br />'; echo 'Time:'.$senton.'<br />'; echo 'Img: <img src="http://www.r.com/members/images/mini/'.$img.'" /><br />'; echo "<hr />\n"; } ?> As for when you're uploading your image, all you need to do is store the image name and extension in users.image. The same goes for when you're displaying it; just retrieve that value. Finally, i should probably add that there are certain oddities in your database structure -- for example, it would be better to store a user id, rather than a user's name, in the tagboard table to identify the user. It is also strange that you have a character limit on the tagboard.nick field of 16, whilst tagboard.account has a limit of 99 and users.Username has a limit of 255, yet all of these are storing the same piece of data! I think there are others, but perhaps that's for a different day. Quote Link to comment Share on other sites More sharing options...
runnerjp Posted May 18, 2008 Author Share Posted May 18, 2008 hehe ok the '.$img.' is not pulling any image at all... it leaves the url just as http://www.r.com/members/images/mini/ thats all also for the imageupload would it be $query = "INSERT INTO `users` (images) VALUES ('$id.$file_ext') WHERE ID = '$id' ON DUPLICATE KEY UPDATE ext = '$file_ext'"; Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 18, 2008 Share Posted May 18, 2008 Did you ever add the field users.image and put anything in it? The above code works fine for me with the structure you gave. As for the UPDATE: $sql = "INSERT INTO `users` (images) VALUES ('".$id.$file_ext."') WHERE ID = '$id'"; Quote Link to comment Share on other sites More sharing options...
runnerjp Posted May 18, 2008 Author Share Posted May 18, 2008 ok made a few tinks in the code and got it working u am tho having trouble with $sql = "INSERT INTO `users` (images) VALUES ('".$id.$file_ext."') WHERE ID = '$id'"; 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 ID = '1'' at line 1 Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 18, 2008 Share Posted May 18, 2008 Sorry, i never really read your SQL code properly. Must be tired. You need to use an UPDATE statement when changing data in a row that already exists. INSERT is used to create a new row. Try: $sql = "UPDATE `users` SET images = '".$id.".".$file_ext."' WHERE ID=$id"; Im assuming that $id is always an integer. Otherwise you'll need quotes around the second $id. Im also assuming that you're using the field name images rather than image. Quote Link to comment Share on other sites More sharing options...
runnerjp Posted May 18, 2008 Author Share Posted May 18, 2008 ahh yes its working... i am trying to make it so that a defaulty image is loaded when regestering 0.jpg but im getting this error SQL/DB Error -- [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 ''0.jpg'' at line 1] $query = $db->query ( "INSERT INTO " . DBPREFIX . "users (`Username` , `Password`, `date_registered`, `Email`, `Random_key`,`image`) VALUES (" . $db->qstr ( $_POST['username'] ) . ", " . $db->qstr ( md5 ( $_POST['password'] ) ).", '" . time () . "', " . $db->qstr ( $_POST['email'] ) . ", '" . random_string ( 'alnum', 32 ) . "'), '0.jpg'" ); rather then open new post can you tell me what the syntax error is as i have Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 18, 2008 Share Posted May 18, 2008 Looks like you closed off the query too soon. Try: <?php $query = $db->query ( "INSERT INTO " . DBPREFIX . "users (`Username` , `Password`, `date_registered`, `Email`, `Random_key`,`image`) VALUES (" . $db->qstr ( $_POST['username'] ) . ", " . $db->qstr ( md5 ( $_POST['password'] ) ).", '" . time () . "', " . $db->qstr ( $_POST['email'] ) . ", '" . random_string ( 'alnum', 32 ) . ", '0.jpg'" ); ?> Quote Link to comment Share on other sites More sharing options...
runnerjp Posted May 18, 2008 Author Share Posted May 18, 2008 nope i still get SQL/DB Error -- [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 '0.jpg'' at line 1] strange eh Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 18, 2008 Share Posted May 18, 2008 Instead of just executing the above query, can you put it in a variable first and echo it? It's just easier to spot an error when you can see exactly what's being executed. Quote Link to comment Share on other sites More sharing options...
runnerjp Posted May 18, 2008 Author Share Posted May 18, 2008 well probelm is its been done as soon as the user signes up like so <?php require_once('settings.php'); require ("members/shoutbox/required.php"); if ( array_key_exists ( '_submit_check', $_POST ) ) { if ( $_POST['username'] != '' && $_POST['password'] != '' && $_POST['password'] == $_POST['password_confirmed'] && $_POST['email'] != '' && valid_email ( $_POST['email'] ) == TRUE ) { if ( ! checkUnique ( 'Username', $_POST['username'] ) ) { $error = 'Username already taken. Please try again!'; } elseif ( ! checkUnique ( 'Email', $_POST['email'] ) ) { $error = 'The email you used is associated with another user. Please try again or use the "Lost password" feature!'; } else { $query = $db->query ( "INSERT INTO " . DBPREFIX . "users (`Username` , `Password`, `date_registered`, `Email`, `Random_key`,`image`) VALUES (" . $db->qstr ( $_POST['username'] ) . ", " . $db->qstr ( md5 ( $_POST['password'] ) ).", '" . time () . "', " . $db->qstr ( $_POST['email'] ) . ", '" . random_string ( 'alnum', 32 ) . ", '0.jpg'" ); $getUser = "SELECT ID, Username, Email, Random_key FROM " . DBPREFIX . "users WHERE Username = " . $db->qstr ( $_POST['username'] ) . ""; $SQL = $db->query ("INSERT INTO tagboard (nick, url, account, message, datetime, ip) VALUES ('Admin', 'http://runningprofiles.com/members/admin', '".$_POST['username']."', 'Welcome to running profiles ', '" . date($ts_format) . "', '$REMOTE_ADDR')" ); if ( $db->RecordCount ( $getUser ) == 1 ) { $row = $db->getRow ( $getUser ); $subject = "Activation email from " . DOMAIN_NAME; $message = "Dear ".$row->Username.", this is your activation link to join our website. In order to confirm your membership please click on the following link: <a href=\"" . APPLICATION_URL . "/confirm.php?ID=" . $row->ID . "&key=" . $row->Random_key . "\">" . APPLICATION_URL . "/confirm.php?ID=" . $row->ID . "&key=" . $row->Random_key . "</a> <br /><br />Thank you for joining"; if ( send_email ( $subject, $row->Email, $message ) ) { $msg = 'Account registered. Please check your email for details on how to activate it.'; } else { $error = 'I managed to register your membership but failed to send the validation email. Please contact the admin at ' . ADMIN_EMAIL; } } else { $error = 'User not found. Please contact the admin at ' . ADMIN_EMAIL; } } } else { $error = 'There was an error in your data. Please make sure you filled in all the required data, you provided a valid email address and that the password fields match one another.'; } } ?> Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 18, 2008 Share Posted May 18, 2008 Think i see it. Try: $query = $db->query ( "INSERT INTO " . DBPREFIX . "users (`Username` , `Password`, `date_registered`, `Email`, `Random_key`,`image`) VALUES (" . $db->qstr ( $_POST['username'] ) . ", " . $db->qstr ( md5 ( $_POST['password'] ) ).", '" . time () . "', " . $db->qstr ( $_POST['email'] ) . ", '" . random_string ( 'alnum', 32 ) . "', '0.jpg'" ); Quote Link to comment Share on other sites More sharing options...
runnerjp Posted May 18, 2008 Author Share Posted May 18, 2008 least im getting a new synthax error now haha SQL/DB Error -- [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 '' at line 1] Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 18, 2008 Share Posted May 18, 2008 Any chance you can modify your query function so that it also echoes the query? Like i said previously, it's much easier to spot an error that way. Quote Link to comment Share on other sites More sharing options...
runnerjp Posted May 18, 2008 Author Share Posted May 18, 2008 ok here you go 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 '' at line 1 Query was:INSERT INTO users (`Username` , `Password`, `date_registered`, `Email`, `Random_key`,`image`) VALUES ('e', '08a44e9d4ff00942b91e', '1211138282', 'yes@itworks.com', 'd8pPTMD8ifjlSMIFOJ969tBbNpDQZrhs', '0.jpg' <?php $sql = "INSERT INTO " . DBPREFIX . "users (`Username` , `Password`, `date_registered`, `Email`, `Random_key`,`image`) VALUES (" . $db->qstr ( $_POST['username'] ) . ", " . $db->qstr ( md5 ( $_POST['password'] ) ).", '" . time () . "', " . $db->qstr ( $_POST['email'] ) . ", '" . random_string ( 'alnum', 32 ) . "', '0.jpg'"; $result = mysql_db_query($database,$sql,$connection) or die(mysql_error().'<br />Query was:'.$sql);?> Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 18, 2008 Share Posted May 18, 2008 Missing closing bracket: $sql = "INSERT INTO " . DBPREFIX . "users (`Username` , `Password`, `date_registered`, `Email`, `Random_key`,`image`) VALUES (" . $db->qstr ( $_POST['username'] ) . ", " . $db->qstr ( md5 ( $_POST['password'] ) ).", '" . time () . "', " . $db->qstr ( $_POST['email'] ) . ", '" . random_string ( 'alnum', 32 ) . "', '0.jpg')"; $result = mysql_db_query($database,$sql,$connection) or die(mysql_error().'<br />Query was:'.$sql) Quote Link to comment Share on other sites More sharing options...
runnerjp Posted May 18, 2008 Author Share Posted May 18, 2008 wahoo done the job lol ty so much for all your help Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.