Allow access to my sql table only if you entered to start with

PHP
1

can anyone tell me how i can allow update and delete to mysql table by email to only be able to make changes on what they put on the table? im using php and have been hunting for days to find a solution any help would be greatly appreciate

2

first step make the email column index.

3

l
An index has been added on email ( Query took 0.0161 sec )
ALTER TABLE place_ad ADD INDEX ( email )
[Inline] [ Edit ] [ Create PHP Code ]

# 	Name 	Type 	Collation 	Attributes 	Null 	Default 	Extra 	Action
1 	adnum 	int(6) 			No 	None 	AUTO_INCREMENT 	Change Change 	Drop Drop 	Show more actions More
2 	postdate 	timestamp 		on update CURRENT_TIMESTAMP 	No 	CURRENT_TIMESTAMP 	ON UPDATE CURRENT_TIMESTAMP 	Change Change 	Drop Drop 	Show more actions More
3 	company 	varchar(40) 	latin1_swedish_ci 		No 	None 		Change Change 	Drop Drop 	Show more actions More
4 	cat 	varchar(40) 	latin1_swedish_ci 		No 	None 		Change Change 	Drop Drop 	Show more actions More
5 	state 	varchar(25) 	latin1_swedish_ci 		No 	None 		Change Change 	Drop Drop 	Show more actions More
6 	detail 	varchar(1000) 	latin1_swedish_ci 		No 	None 		Change Change 	Drop Drop 	Show more actions More
7 	buyerseller 	varchar(255) 	latin1_swedish_ci 		No 	None 		Change Change 	Drop Drop 	Show more actions More
8 	firstname 	varchar(25) 	latin1_swedish_ci 		No 	None 		Change Change 	Drop Drop 	Show more actions More
9 	lastname 	varchar(25) 	latin1_swedish_ci 		No 	None 		Change Change 	Drop Drop 	Show more actions More
10 	phone1 	varchar(255) 	latin1_swedish_ci 		No 	None 		Change Change 	Drop Drop 	Show more actions More
11 	phone2 	varchar(255) 	latin1_swedish_ci 		No 	None 		Change Change 	Drop Drop 	Show more actions More
12 	email 	varchar(255) 	latin1_swedish_ci 		No 	None 		Change Change 	Drop Drop 	Show more actions More

With selected: Check All / Uncheck All With selected:
Print view Print view Propose table structure Propose table structureDocumentation
Add columnAdd column(s) At End of Table At Beginning of Table After

  • Indexes
    Information
    Space usage Data 1.4 KiB
    Index 15 KiB
    Total 16.4 KiB
    Row Statistics Format dynamic
    Collation latin1_swedish_ci
    Rows 12
    Row length ø 118
    Row size ø 1,398 B
    Next autoindex 62
    Creation Jul 14, 2014 at 11:08 AM
    Last update Jul 14, 2014 at 11:08 AM

Ok done

4

If you want to limit your users options so that they can only update information that they supplied in the first place, you’re going to need to keep a record of who added each row. You’re probably going to need some kind of username and password system to be sure that the person wanting to update information is who you think they are, once they have confirmed their identity by logging in to the site, you can simply compare their ID against the ‘creator’ ID that you stored, and if they’re not the same, don’t give them the option to update.

Or did I misunderstand the question?

5

Yes you understand perfectly I have a place where they sign up as members using email and a place where they can place a ad or view ads I’m trying now to create a place to update or delete an ad but it shows all ads so anyone can update or delete I need to somehow online bring up ads entered by there email just not sure how to put on webpage to control update and delete

6

Well, you’ll have to make them login to the site using their email address and a password. Once you’ve got that information, you can lookup their unique user id in your ‘members’ table based on the email address and password, and use that information to select which ads appear in the query when you’re offering them the opportunity to update or delete. You could lookup the ads based on their email address, but it’s not a normal way to do it - if they change their email address at some point, it’s more work for example.

So your members table would have a unique id field, along with their username (or email address, or both) and password. That unique id field is stored against each ad, which is how you identify which they can update and which they can only look at in your SELECT statement.

So pseudo-code might be something like:


// get email and password from form
$result = query("select * from members where useremail = $_POST['email']";
if ($result) {
   $userid = $row['userid'];
   $ads = query("select * from ads where userid = $userid");
   if ($ads) {
      // display all the ads with update and delete buttons
      }
   else {
      echo "No ads for that user";
      }
   }
else
   {
   echo "Could not find user id";
   }

Obviously you’d need to change that to proper PDO or mysqli calls, just illustrating the idea.

7