Doug Posted June 9, 2012 Share Posted June 9, 2012 Hello, I am creating a website where users can "claim" their business by clicking on a link. I have managed to code so that users can claim their business or get a message that the business has already ben claimed by another user. Where I'm coming stuck is if a user wants to claim more than one business (I want to limit each user to one). I have made the MySQL table for username unique so that if a user does try to claim more than one business they get the MySQL objection. My question is how do I, instead of seeing this message, redirect to a "you have already claimed a business message"? I am at a loss as how to do this or is my logic wrong? Any help greatly appreciated. This is the relevant code I already have: $username1 = $_SESSION['username']; if (($username != ($username1)) && (!empty($username))) { echo '<p class="error"> This profile is taken</p>'; ?> <?php echo('<p class="login">You are logged in as ' . $username1 . '. <a href="logout3.php">Log out</a>.</p>'); ?> <?php echo('<p class="login">The owner of this account is ' . $username . '. </p>'); ?> <p><a href="index5.php">Return to homepage</a></p> <?php exit(); } else { echo '<p class="error">There was a problem accessing your profile.</p>'; }} mysqli_close($dbc); ?> Quote Link to comment https://forums.phpfreaks.com/topic/263920-php-redirect-to-avoid-mysql-objection/ Share on other sites More sharing options...
scootstah Posted June 9, 2012 Share Posted June 9, 2012 Why would users be seeing MySQL errors in the first place? Quote Link to comment https://forums.phpfreaks.com/topic/263920-php-redirect-to-avoid-mysql-objection/#findComment-1352489 Share on other sites More sharing options...
Doug Posted June 9, 2012 Author Share Posted June 9, 2012 username in MySQL is set to unique. If the user tries to claim another business MySQL objects as it is replicating a username which should be unique. So you get for example: Query UPDATE companies set name = 'Business', phone = '012345, address1 = 'first line', address2 = 'Second line', postcode = 'postcode', email = 'email entered', webadd = 'web entered', username = 'doug', cat = 'bakers' WHERE user_id = '12 ' Failed with error: Duplicate entry 'doug' for key 'username' On line: 203 This is the page I would like to avoid if possible? Quote Link to comment https://forums.phpfreaks.com/topic/263920-php-redirect-to-avoid-mysql-objection/#findComment-1352505 Share on other sites More sharing options...
boompa Posted June 9, 2012 Share Posted June 9, 2012 Instead of doing an UPDATE query, do a SELECT first, and don't let them claim it if it already exists. Quote Link to comment https://forums.phpfreaks.com/topic/263920-php-redirect-to-avoid-mysql-objection/#findComment-1352511 Share on other sites More sharing options...
Pikachu2000 Posted June 9, 2012 Share Posted June 9, 2012 Use an INSERT query, and since MySQL returns a duplicate key error, you can check for that error upon executing the query. If the error is present, show the user an error message that you create, NOT an error message returned by the database. As an example: $query = "INSERT INTO table (user_id, business_name, etc) VALUES ($user_id, '$business_name', '$etc')"; if( !$result = mysql_query($query) ) { if( mysql_errno() === 1062 ) { echo "You have already claimed a business, moron."; } else { echo "Sorry, the database gagged and puked up your input."; } } else { // Query succeeded, so here you can tell the user it worked, or redirect, etc. } Quote Link to comment https://forums.phpfreaks.com/topic/263920-php-redirect-to-avoid-mysql-objection/#findComment-1352535 Share on other sites More sharing options...
Doug Posted June 10, 2012 Author Share Posted June 10, 2012 Hi. That is nearly there. The problem is I am now unable to access the one business I have claimed and I get the following message with any other business I try to claim: Warning: mysql_query() [function.mysql-query]: Can't connect to MySQL server on 'localhost' (10061) in C:\Program Files (x86)\EasyPHP5.2.10\www\OneSevenoaks\editbusprofile9.php on line 205 Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in C:\Program Files (x86)\EasyPHP5.2.10\www\OneSevenoaks\editbusprofile9.php on line 205 Sorry, the database gagged and puked up your input. Any ideas? Modified code below: if (!$error) { if (!empty($name)&& !empty($phone) && !empty($address1) && !empty($address2)) { // Only set the picture column if there is a new picture if (!empty($new_picture)) { //if (!empty($postcode)){ $query = "UPDATE companies SET name = '$name', phone = '$phone', address1 = '$address1', address2 = '$address2', postcode = '$postcode', " . " email = '$email', webadd = '$webadd', picture = '$new_picture', username = '$username', cat = '$cat' WHERE user_id = '" . $row['user_id'] ."'"; if( !$result = mysql_query($query) ) { if( mysql_errno() === 1062 ) { echo "You have already claimed a business, moron."; } else { echo "Sorry, the database gagged and puked up your input."; } } else { $query = "UPDATE companies set name = '$name', phone = '$phone', address1 = '$address1', address2 = '$address2', postcode = '$postcode', " . " email = '$email', webadd = '$webadd', username = '$username', cat = '$cat' WHERE user_id = '" . $row['user_id'] ." '"; }}} mysqli_query($dbc, $query) or die("<br>Query $query<br>Failed with error: " . mysqli_error($dbc) . '<br>On line: ' . __LINE__); if( !$result = mysql_query($query) ) { if( mysql_errno() === 1062 ) { echo "You have already claimed a business, moron."; } else { echo "Sorry, the database gagged and puked up your input."; ?> <P>GO <a href="index5.php">Home</a></p> <?php }} else { // Confirm success with the user echo 'USER ID = ' . $row["user_id"] . ''; ?> <br /> <?php echo '<p>Your profile has been successfully updated. Would you like to <a href="viewprofile7.php">view your profile</a>?</p>'; } mysqli_close($dbc); exit(); } Quote Link to comment https://forums.phpfreaks.com/topic/263920-php-redirect-to-avoid-mysql-objection/#findComment-1352634 Share on other sites More sharing options...
PFMaBiSmAd Posted June 10, 2012 Share Posted June 10, 2012 Your current errors are because you haven't made a database connection before the mysql_query() statement and php is trying to make a database connection using default values, which generally fails. Where in your code are you making a database connection? Quote Link to comment https://forums.phpfreaks.com/topic/263920-php-redirect-to-avoid-mysql-objection/#findComment-1352681 Share on other sites More sharing options...
Pikachu2000 Posted June 10, 2012 Share Posted June 10, 2012 You need to change the code I posted to use the mysqli extension functions. I didn't realize that was what you were using. Quote Link to comment https://forums.phpfreaks.com/topic/263920-php-redirect-to-avoid-mysql-objection/#findComment-1352689 Share on other sites More sharing options...
Doug Posted June 11, 2012 Author Share Posted June 11, 2012 I have made the changes...I get one error now: Warning: mysqli_query() expects at least 2 parameters, 1 given Obviously I'm missing something. I tied adding $dbc and swapping the parameters around but these didn't work. if (!empty($name)&& !empty($phone) && !empty($address1) && !empty($address2)) { // Only set the picture column if there is a new picture if (!empty($new_picture)) { //if (!empty($postcode)){ $query = "UPDATE companies SET name = '$name', phone = '$phone', address1 = '$address1', address2 = '$address2', postcode = '$postcode', " . " email = '$email', webadd = '$webadd', picture = '$new_picture', username = '$username', cat = '$cat' WHERE user_id = '" . $row['user_id'] ."'"; if( !$result = mysqli_query($query) ) { if( mysql_errno() === 1062 ) { echo "You have already claimed a business, moron."; } else { echo "Sorry, the database gagged and puked up your input."; } } else { $query = "UPDATE companies set name = '$name', phone = '$phone', address1 = '$address1', address2 = '$address2', postcode = '$postcode', " . " email = '$email', webadd = '$webadd', username = '$username', cat = '$cat' WHERE user_id = '" . $row['user_id'] ." '"; }}} mysqli_query($dbc, $query) or die("<br>Query $query<br>Failed with error: " . mysql_error($dbc) . '<br>On line: ' . __LINE__); if( !$result = mysqli_query($query) ) { if( mysql_errno() === 1062 ) { echo "You have already claimed a business, moron."; } else { echo "Sorry, the database gagged and puked up your input."; ?> 1<P>GO <a href="index5.php">Home</a></p> <?php }} else { // Confirm success with the user echo 'USER ID = ' . $row["user_id"] . ''; ?> <br /> <?php echo '<p>Your profile has been successfully updated. Would you like to <a href="viewprofile7.php">view your profile</a>?</p>'; } mysqli_close($dbc); exit(); } Quote Link to comment https://forums.phpfreaks.com/topic/263920-php-redirect-to-avoid-mysql-objection/#findComment-1352985 Share on other sites More sharing options...
Pikachu2000 Posted June 11, 2012 Share Posted June 11, 2012 mysqli_query( $dbc, $query ); is the right format. Quote Link to comment https://forums.phpfreaks.com/topic/263920-php-redirect-to-avoid-mysql-objection/#findComment-1352989 Share on other sites More sharing options...
Doug Posted June 12, 2012 Author Share Posted June 12, 2012 Sorry....that doesn't work...the user goes straight to "Your profile has been successfully updated" which is wrong (and also untrue!) they should get the one of the two earlier messages Thanks so much for your continued help Doug Quote Link to comment https://forums.phpfreaks.com/topic/263920-php-redirect-to-avoid-mysql-objection/#findComment-1353248 Share on other sites More sharing options...
Pikachu2000 Posted June 12, 2012 Share Posted June 12, 2012 That code is really all over the place and unnecessarily redundant, and you didn't change all of the functions to use the mysqli extension. It looks like the only reason you have 2 different queries is to update a picture if it's present, and skip it if it isn't. In that case, all you really need to do is make part of the query string conditional, based on that factor. See if this code makes more sense to you. <?php if (!empty($name)&& !empty($phone) && !empty($address1) && !empty($address2)) { // Start building the query string $query = "UPDATE companies SET name = '$name', phone = '$phone', address1 = '$address1', address2 = '$address2', postcode = '$postcode', email = '$email', webadd = '$webadd', username = '$username', cat = '$cat'"; // Only concatenate the picture column to the query string if there is a new picture $query .= !empty($new_picture) ? ", picture = '$new_picture'" : ''; // concatenate WHERE clause to query string $query .= " WHERE user_id = {$row['user_id']}"; // execute query and handle any errors if( !$result = mysqli_query($query) ) { if( mysqli_errno($dbc) === 1062 ) { echo "You have already claimed a business, moron."; } else { // You should add code to log the actual database error to your error logs here. echo "Sorry, the database gagged and puked up your input."; } } else { // query executed, so for the time being check to make sure something actually happened echo "<br>**** The query executed and updated " . mysqli_affected_rows($dbc) . 'records. ****<br>'; } } else { echo 'One or more of the required fields were left blank.'; } Quote Link to comment https://forums.phpfreaks.com/topic/263920-php-redirect-to-avoid-mysql-objection/#findComment-1353292 Share on other sites More sharing options...
Doug Posted June 14, 2012 Author Share Posted June 14, 2012 yes, I understand that. Thank you so much for your help Quote Link to comment https://forums.phpfreaks.com/topic/263920-php-redirect-to-avoid-mysql-objection/#findComment-1353855 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.