saving data to another database

[thepresidentis]

I have a database that users can search for concert listings.
I also have a user registration database where users can log on.
I want to be able to have a link in the search results for the concert listings to save the show information to their profile.
I have thought long and hard about this but I am still green on PHP
I thought i could create a link next to the each concert listing (SAVE THIS EVENT) and then send the event info to a form that automatically inserts it into their saved shows column in the user reg database.
Am I even close?
If so how would i send the info to the user reg database?

[Motoma]

I have a database that users can search for concert listings.
I also have a user registration database where users can log on.
I want to be able to have a link in the search results for the concert listings to save the show information to their profile.
I have thought long and hard about this but I am still green on PHP
I thought i could create a link next to the each concert listing (SAVE THIS EVENT) and then send the event info to a form that automatically inserts it into their saved shows column in the user reg database.
Am I even close?
If so how would i send the info to the user reg database?

I would create another table, consisting of UserID and EventID. When a user saves an event, put it in the table.

[thepresidentis]

I would create another table, consisting of UserID and EventID. When a user saves an event, put it in the table.

okay. Thanks
one more question though,
how would I send the data from the concert listing to the database?
i was thinking that i could create the save this show link and then if they were to press it then it would call a function?
that would use insert into.
but my problem is that i am still new to coding with php, and I am not sure how to pass the data stored in the variables to a function that will use insert into.
any help would be such a life saver!!!!
here is what I have so far....
[CODE=php]

echo "Results<BR >";

$count = 1 + $s;



// this registers the information into a variable

while ($row= mysql_fetch_arr ay($result)) {

$title = $row["City"];
$title2 = $row["State"];
$title3 = $row["Metro_Area "];
$title4 = $row["Date_Input "];
$title5 = $row["Band_Or_Event_ Name"];
$title6 = $row["Genre"];
$title7 = $row["Genre_2"];
$title8 = $row["Genre_3"];
$title9 = $row["Genre_4"];
$title14 = $row["Venue"];
$title10 = $row["Other_Info "];
$title11 = $row["MP3_Link"];
$title12 = $row["Link_to_Direct ions"];
$title13 = $row["Age_Restrictio ns"];



//this displays the information stored in the variables

echo "$count.)&nbsp; &nbsp;&nbsp;&nb sp;&nbsp;&nbsp; &nbsp;&nbsp;&nb sp;&nbsp;&nbsp;
Date : <FONT color='red'>$ti tle4</FONT><BR>
&nbsp;Band or event : <FONT color='red'>$ti tle5</FONT>
&nbsp;&nbsp;&nb sp;&nbsp;&nbsp; &nbsp;&nbsp;&nb sp;&nbsp;&nbsp; &nbsp;
&nbsp;&nbsp;Ven ue : <FONT color='red'>$ti tle14</FONT><BR>
&nbsp;&nbsp;&nb sp;&nbsp;&nbsp; &nbsp;&nbsp;&nb sp;&nbsp;&nbsp; &nbsp;
&nbsp;&nbsp;&nb sp;&nbsp;City : <FONT color='blue'>$t itle</FONT><BR>
&nbsp;&nbsp;&nb sp;&nbsp;&nbsp; &nbsp;&nbsp;&nb sp;&nbsp;&nbsp; &nbsp;
&nbsp;&nbsp;&nb sp;&nbsp;State : <FONT color='red'>$ti tle2</FONT> <BR>
&nbsp;&nbsp;&nb sp;Metro Area : <FONT color='red'>$ti tle3</FONT><BR>
&nbsp;&nbsp;&nb sp;&nbsp;&nbsp; &nbsp;&nbsp;&nb sp;&nbsp;&nbsp; &nbsp;
&nbsp;Genre : <FONT color='red'>$ti tle6</FONT><BR>
&nbsp;&nbsp;Oth er genre's : <FONT color='red'>$ti tle7,&nbsp;$tit le8,&nbsp;$titl e9</FONT><BR>
&nbsp;Age restriction : <FONT color='red'>$ti tle13</FONT><BR>
&nbsp;Other information : <FONT color='red'>$ti tle10</FONT><BR>
&nbsp;&nbsp;&nb sp;&nbsp;&nbsp; &nbsp;Link to mp3 : <FONT color='blue'>$t itle11</FONT><BR>
&nbsp;Link to directions : <FONT color='blue'>$t itle12</FONT></a><BR><BR>";

//this is what i used to create a link but i would rather use a function to process the insert into.

echo "[<a href=\"http://www.showsniffer .com/fgen/use/Submit/SaveShow.html\" >Save This Event</a>]";

$count++ ;

}



$currPage = (($s/$limit) + 1);
[/CODE]

[thepresidentis]

also could I just register the primary Key as a variable and just save the Primary Key in the saved events column in the USER REG DB?
I am Hoping this will save disk space rather than saving all of the show information again.

[pbmods]

I'm moving this thread to the MySQL forum 'cause that's what it's turning into!

Welcome to database normalization 101.

Alrightey. So you have concerts, and you have Users. Users can save as many events as they want, and more than one User can save any given concert. You have what we like to call a "many-to-many" relationship.

Many-to-many relationships between two tables actually requires three tables:

• Data_Concerts
• Data_Users
• Map_UserConcert

Map_UserConcert tells you what Concerts each User has saved:

[CODE=mysql]
mysql> SELECT * FROM `Map_UserConcer t` LIMIT 5;
+--------+-----------+
| userid | concertid |
+--------+-----------+
| 1| 158|
| 8| 32|
| 2| 60|
| 12| 204|
| 1| 1|
+--------+-----------+
5 rows in set (0.0000 sec)
[/CODE]


As you can see (at least by this sample of 5 rows), User #1 saved concerts 1 & 158; User #2 saved concert 60; User #8 saved concert 32 and User #12 saved concert 204.

For best results, you'll be wanting to create a view:

[code=mysql]
CREATE VIEW `View_SavedConc erts` AS SELECT * FROM (`Data_Users` LEFT JOIN `Map_UserConcer t` USING(`userid`) LEFT JOIN `Data_Concerts` USING(`concerti d`)) ORDER BY `userid`, `concertid` ASC;
[/code]

Which would give you something like this:

[code=mysql]
mysql> SELECT * FROM `View_SavedConc erts` WHERE `userid` = '1' AND `Date_Input` > NOW();
+--------+------+-----------+---------+-------+---------+
| userid | Name | concertid | City | State | etc.... |
+--------+------+-----------+---------+-------+---------+
| 1| John | 158 | Chicago | IL |
+--------+------+-----------+---------+-------+
| etc... |
+--------+
[/code]

And boom; all in one table, you have your User and concert data.

[MMcCarthy]

This tutorial may help

Normalization and Table structures

Mary

[thepresidentis]

Thanks for all the info, it is really helping...
I am still a little confused(sorry)
on map_concerts does there need to be an index?
I have 2 fields
userid and concert id.

[thepresidentis]

p.s the users db is a seperate database than the concerts database

here is my structure

Database: EVENT DATA
Table:Events
then there is a seperate fields for State, city, metro area, etc...

Database: UserDB
Table: Active_guests
Table:Active_Us ers
Table:Banned_us ers
Table:Map_userc oncerts
Table:Users

[MMcCarthy]

Thanks for all the info, it is really helping...
I am still a little confused(sorry)
on map_concerts does there need to be an index?
I have 2 fields
userid and concert id.

Assuming you are talking about a JOIN table to split up a many to many relationship then it would have a composite primary key made up of both foreign keys.

[thepresidentis]

would it be possible to create a clickable link to envoke a variable called

$SAVE = mysql_connect("host","username","password");
mysql_select_db("userdb");
$query2="insert into map_userconcert (userid,concertid) values ('".$userid."','".$concertid."')";
mysql_query($query2);

[pbmods]

would it be possible to create a clickable link to envoke a variable called ....

To do that, create a link to another PHP script that executes that statement.

E.g.,
[code=html]
<a href="save.php? concertid=4">Sa ve this Concert</a>
[/code]

Then in save.php:
[code=php]
mysql_connect(" host","username ","password ");
mysql_select_db ("userdb");
$query2="insert into map_userconcert (userid,concert id) values ('".$userid."', '".intval($_ GET['concertid'])."')";
mysql_query($qu ery2);
[/code]