问题描述:

I am practicing PHP with MySQL, and I am creating a webpage that lists all the details from table(s) from a query - this part works perfectly. I then want the user to be able to click a link from the table, which will direct the user to a CRUD page for that specific table entry.

The code that I have, works fine in conjunction with my original page, however, since there are 3 tables in which I am trying to join together, it then complicates things for me. The working code that I have now, only works with one table. I am struggling to come up with ideas, on how to change my code, so that it will facilitate the three tables I have with joins.

Here are the tables that I have:

ACT GIG VENUE

sname sname sname (these do not contain same values.)

id(2) id id(1)

act(2)

venue(1)

And now the code breakdown.

This is the code that will allow me update an entry within my table(although I want it to update 3 instead of one)

if (!count($errors)) {

// Data is valid and can be committed to database

if ('Update' == $_REQUEST['action']) {

$show = 'table';

// Data for gig can be safely updated

$sql = 'UPDATE gig SET sname=:sname, start=:start

WHERE id=:id';

$res = $db->prepare($sql);

if (!$res->execute(array(':sname' => $details['sname'],

':id' => $details['id'],

':start' => $details['start'],

))) {

$errors['sql'] = sprintf("%s (id %s) could not be updated",htmlentities($_POST['sname']),$_POST['id']);

$show = 'form';

} else {

$status = sprintf("%s updated",htmlentities($details['sname']));

}

And now the code for creating a new entry from the webpage. Again this code only inserts data for the one table and not 3 that I want. But we'll get to that at the bottom.

} else { // Create action

// Data for gig can be safely created

$sql = 'INSERT INTO gig (id,sname,start)

VALUES (:id,:sname,:start)';

$res = $db->prepare($sql);

if (!$res->execute(array(':sname' => $details['sname'],

':id' => $details['id'],

':start' => $details['start'],

))) {

$errors['sql'] = sprintf("%s (id %s) could not be created (Has the id already been used?)",htmlentities($_POST['country']),$_POST['id']);

$show = 'new';

} else {

$status = sprintf("%s created",htmlentities($details['sname']));

}

}

}

So I am trying to aim to get, the three colums(sname) implemented within my code, I know they all clash, but I got round that when I managed to print all the details from the 3 comments.

This was the code.

SELECT g.id AS gig_id, g.sname AS gig_name, v.sname AS venue_name, a.sname AS act_name FROM gig AS g, venue AS v, act AS a WHERE g.venue=v.id AND g.act=a.id

Basically what I am asking is, how would I tweak the above statement to fit in with my CRUD statements, I hope that I have the question part solved, and I just need a steer in the right direction?

Thanks in advance for any help.

网友答案:

You can use queries something like the following to accomplish this:

/* insert */
insert into venue values ();
set @gig_venue := LAST_INSERT_ID();
insert into act values ();
set @gig_act := LAST_INSERT_ID();
insert into gig values(@gig_venue, @gig_act /*, etc */);

/* update */
select venue, act from gig where id = @gig_id into @venue, @act;
update venue set foo=1 where id = @venue;
update act set bar=1 where id = @act;
update gig set baz=1 where id = @gig_id;

/* update in a single statement (no way to do with insert though)*/
update gig
join venue on venue.id = gig.venue
join act on act.id = gig.act
set foo=1,
    bar=2;

However, you'll either need to put them in a transaction or be very careful to implement manual rollback on failure.

相关阅读:
Top