php redirect to avoid MySQL objection

[Doug]

 

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);


  ?>

 

 

[scootstah]

Why would users be seeing MySQL errors in the first place?

[Doug]

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?

[boompa]

Instead of doing an UPDATE query, do a SELECT first, and don't let them claim it if it already exists.

[Pikachu2000]

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.
}

[Doug]

 

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();
    
  }

[PFMaBiSmAd]

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?

[Pikachu2000]

You need to change the code I posted to use the mysqli extension functions. I didn't realize that was what you were using.

[Doug]

 

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();
    
  }

[Pikachu2000]

mysqli_query( $dbc, $query ); is the right format.

[Doug]

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

 

[Pikachu2000]

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.';
}

[Doug]

yes, I understand that. Thank you so much for your help