select table from dropdown menu

[Titanium]

Hello,I am trying to create a script that will allow people to input data into a database using a web form.I have a list of my database tables in a dropdown menu. My question is how can I make it so that when a person clicks on one of those tables in the dropdown menu, the script knows that the user wants to edit that specific table? Here is my dropdown menu code:

<form ACTION=URI><select name="dropdown" onchange="insertinfoform.php" style="color:#383838; font-size: 8pt; background:#CCCCFF"><option>Stuff</option><option></option><? include("insertinfoform.php") ?></select></form>

Obviously I'm not sure what to do with the 'onchange' part. Here's the insertinfoform.php code. It displays the table names in the dropdown menu. I took out the password, username, and database info just to be safe.

<?$sql = "show tables";$result = connect($sql);while ($rows = mysql_fetch_array($result)){  echo "<option>";  echo $rows[0]."</option><br>";}function connect($sql){    $username   = "username";    $pwd        = "password";    $host       = "localhost";    $dbname     = "database";    //echo "commnecing connection to local db<br>";       if (!($conn=mysql_connect($host, $username, $pwd)))  {        printf("error connecting to DB");        exit();    }    $db2=mysql_select_db($dbname,$conn);       $result = mysql_query($sql);    if ($result){        return $result;    }else{      $error = mysql_error();        echo "Can't complete query because $error";        die();       }}   ?>

So let's say I have 2 tables in a database - music and food. Those 2 names would appear in the dropdown menu. If I select 'music' from the menu, then the script would transfer me to the 'music' table so that I can edit the 'music' table with the rest of the form and vice versa with the 'food' table. I hope this makes sense.

[justsomeguy]

Anything inside an onchange attribute needs to be Javascript code, such as a Javascript function to run or one or more statements to evaluate. The Javascript function has to be a locally-defined function that the browser has access to. Since Javascript is a client-side language, you can't have any database statements or PHP code inside the Javascript. Anything coming from PHP needs to be added to the Javascript code before the client interacts with it. That means that if you have, for example, two dropdown menus and you want the second menu to update based on what you chose from the first one, and that information needs to be dynamic, then you need to have PHP write out all of the dynamic information into some Javascript code that will be executed using the onchange event.If all you want to do is create a dropdown menu with the database tables as options, then that's easy to do. You already have the code to do that by including your file that creates the options inside the select tag. If you want to show the columns that the table has, where they choose a table and the list of fields that they can fill out changes based on that table, then you will need to write the Javascript that runs onchange that 1) determines which table they chose and 2) removes all fields from the form and adds the table-specific fields. You can use PHP to dynamically write that Javascript code, but that's what is going to need to happen.

If I select 'music' from the menu, then the script would transfer me to the 'music' table so that I can edit the 'music' table with the rest of the form

The script is not going to "transfer" you to a database table, there is no interface between Javascript and the database. You need to do all of that yourself by having PHP write out the table attributes (field names, types, sizes, etc) and then using Javascript to build an HTML form based on the selected table.Also, this doesn't even take into account existing data in the table. If you want to edit what is there that's a whole different issue. It sounds like what you want is a page that lists everything in the table now, with a link next to each entry to edit that entry or delete it, and another form to add a new entry. In fact, if all you're doing is editing tables in the database it would be better to use an application that does exactly that like phpMyAdmin.

[Titanium]

If all you want to do is create a dropdown menu with the database tables as options, then that's easy to do. If you want to show the columns that the table has, where they choose a table and the list of fields that they can fill out changes based on that table, then you will need to write the Javascript that runs onchange that 1) determines which table they chose and 2) removes all fields from the form and adds the table-specific fields. You can use PHP to dynamically write that Javascript code, but that's what is going to need to happen.

Yes, I want to create a dropdown menu that lists the different tables in the database. Then when I click on the table in the dropdown menu, I want a script to load the table so that I have the ability to add rows. I will be adding in the rows and data through a form that contains input fields. All the tables will have the same rows, but the data in the rows will be different. So yes, it would be helpful if the current data (if any) does show up in the input fields.If there are no rows in the table, then I want the web form to create the rows and include the data. If there are rows and data, then I would like that data to be presented in the input fields so that they can easily be modified. I'm not sure if this is possible or not. If not, what would be another alternative for modifying the data?When I click on a different table in the database, I would like that table's data to be presented instead. So the page would be refreshed with the new table's data loaded.

It sounds like what you want is a page that lists everything in the table now, with a link next to each entry to edit that entry or delete it, and another form to add a new entry.

Yes, that is what I want. I do understand that I can easily use phpMyAdmin to edit the database, but there are going to be a lot of other people that I want to grant access to the database. This appears to be the only option because I cannot give them access to phpMyAdmin. That is why I want to create something that is basically like phpMyAdmin.So I already have the script that lists the tables in the options. Now what I need is to write a script that loads up the table that is selected and allows me to add data into the table. If the tables are empty, then nothing will appear in the input fields in the form. I can easily add in the data, and submit it. The data then can be added into the tables. Each input field will be a different row that will go into the table. I can do this by using a PHP script.So what types of scripts would I need to write? Obviously I would need to write the PHP script above. I know other scripts will need to be written, but what type of information would I add into each script? Hopefully this makes more sense.

[Synook]

That is why I want to create something that is basically like phpMyAdmin.

Why not? The best thing to do would be to poke around at the phpMyAdmin code (it is all open source, just go to the directory) and also in the phpMyAdmin web site http://www.phpmyadmin.net/home_page/index.php which has many useful resources.

[justsomeguy]

If you want to set this up yourself you'll need a page with basically 3 parts. Each part will be a different form. One part will be the dropdown menu where you select a table and hit a submit button. The second part will be a display of all of the existing information in the table with edit and delete links, and will only appear if they have already chosen a table. The third part will be a form to add a new row and will also only be available if they chose a table.The first form you already have basically, but it needs to be changed a little bit.

<form action="<?php echo $PHP_SELF;?>"><select name="table" style="color:#383838; font-size: 8pt; background:#CCCCFF"><option>Stuff</option><option></option><?php include("insertinfoform.php") ?></select><input type="submit" value="Select"></form>

That will submit to the same page with whatever the user chose. You need to modify your insertinfoform file to include a value with each option.

while ($rows = mysql_fetch_array($result)){  echo "<option value=\"{$rows[0]}\">";  echo $rows[0]."</option><br>";}

You can use code like this to check if they have selected a table:

if (isset($_POST['table']) || isset($_GET['table'])){  //table selected}

If a table is selected, you need to select everything from the table and write it to the page. The edit and delete links will be a link to the same page and will need to include the selected table and the row ID of whichever row they clicked on.<a href="<?php echo $PHP_SELF; ?>?table=<?php echo $table; ?>&id=<?php echo $row['id']; ?>&mode=edit">Edit</a><a href="<?php echo $PHP_SELF; ?>?table=<?php echo $table; ?>&id=<?php echo $row['id']; ?>&mode=delete">Delete</a>You can show a confirmation if they click delete to ask if they really want to, and if they clicked edit you can check as you write out the table rows and if the row ID matches the ID that was in the querystring and the mode is set to edit then you can show a form for them to edit that row instead of just showing the data. At the end you can include an insert form with a mode like "insert" or something where you would check what the mode is and (if it equals "insert") then you would get the form data from the insert form and insert a new row into the selected table.If you have worked with AJAX, that would be something that you could use to do this same thing without refreshing the page. Selecting a table would cause the data to appear below, clicking the edit link would replace the row data with a form, etc.

[Titanium]

You can use code like this to check if they have selected a table:

if (isset($_POST['table']) || isset($_GET['table'])){  //table selected}

If a table is selected, you need to select everything from the table and write it to the page.

OK, I understand that the SELECT function will have to be used. So something like mysql_query(SELECT picslink FROM $table); will work. Now "picslink" is one of the rows that are in the table. Would I do that for each one of the rows in the table? How would I write the data onto the input fields?One thing that I was trying to do was to make the script for adding the data into the database if there were no rows/data already in the table.I basically had the <form action="insertform2.php" method="post"> as the first line. Then for each input field, I used <input type="text" name="picslink" />Then in insertform2.php, I used variables for the names. For example, $picslink = $_POST['picslink']; What I was trying to do was to use that to add the data into the table.

$table = $_POST['name'];$picslink = $_POST['picslink'];$result = mysql_query("INSERT INTO $table (id, picslink) VALUES (NULL ,'0','$picslink')" or die(mysql_error()));if ($result) {  echo "Everything was added successfully!";}else {  $error = mysql_error();  echo "There was a problem with adding data.  Please contact the system's administrator about this problem.";  echo $error;  die();}

What I was trying to do was to get the data inputted into the table when the Submit button was clicked. The "Everything was added successfully!" message appeared, but the data wasn't inputted. It should have sent the data into the "name" table.

[justsomeguy]

I'm not sure why it's not saying there's an error, because there is. In your insert statement you have 2 columns in the field list and 3 values, that's an error. If the id column is an autonumber then you don't need to include it in the field list. You'll also want to use mysql_real_escape_string to protect against SQL injection attacks, you can do a Google search for SQL injection to learn about that.

$result = mysql_query("INSERT INTO $table (picslink) VALUES ('" . mysql_real_escape_string($picslink) . "')" or die(mysql_error()));

[Titanium]

Thank you, I was able to make it so that the data was entered into the database. Now that that's done, I want to focus on the previous script where the data is automatically added into the fields if it exists in the table.

if (isset($_POST['table']) || isset($_GET['table'])){$display = mysql_query("SELECT * FROM table");while($row = mysql_fetch_array($display))  {  echo $row['picslink'];  echo "<br />";  }}

That's what I have so far. Right now I have it so that the value of 'picslink' is displayed. Now how would I get it to be displayed in the picslink input field?

[Synook]

You could use PHP to generate an input

if (isset($_POST['table']) || isset($_GET['table'])){$display = mysql_query("SELECT * FROM table");while($row = mysql_fetch_array($display))  {  echo "Piclink: <input type=\'text\" name=\"picslink\" value=\"{$row['picslink']}\" />";  echo "<br />";  }}

That would be useful for UPDATE queries.

[Titanium]

Where do I put that code? Right now, it's in my insertinfoform.php file, the file for the dropdown menu. I have a separate PHP file called insertform2.php where the code for adding the data to the database is at.

form action="<?php echo $PHP_SELF;?>"><select name="table" style="color:#383838; font-size: 8pt; background:#CCCCFF"><option>Stuff</option><option></option><?php include("insertinfoform.php") ?></select><input type="submit" value="Select" style="color:#FFFFFF; font-size: 8pt; background:#383838"></form><br><br><table border="0" width="100%" cellspacing="0" cellpading="4"><tr><td align="center"><form action="insertform2.php" method="post"><b>Picslink:</b> <input type="text" name="picslink" /></td><td align="center"><b>stuff:</b> <input type="text" name="stuff" /></td></tr>

That's what the HTML form looks like.

$sql = "show tables";$result = connect($sql);while ($rows = mysql_fetch_array($result)){  echo "<option value=\"{$rows[0]}\">";  echo $rows[0]."</option><br>";}if (isset($_POST['table']) || isset($_GET['table'])){$display = mysql_query("SELECT * FROM table");$display2 = mysql_query("UPDATEwhile($row = mysql_fetch_array($display))  {  echo "Piclink: <input type=\"text\" name=\"picslink\" value=\"{$row['picslink']}\" />";  echo "<br />";  }}

That's what a part of my insertinfoform.php file looks like. As you can see, I have your code displayed there. I'm not sure if I have it displayed in the right place though because the table that I selected doesn't update itself with the data. The data from the table are suppose to go into the input fields that are in the form. So where exactly would I put that block of code?

[justsomeguy]

It doesn't go in that file, that file is for displaying the options in the table dropdown, nothing else. Since it echos its values that's all you can use it for. The loop needs to go in the file that is displaying the form, add whatever else into the loop so that each time through the loop it writes out the entire row for the table.

[Titanium]

Well, then that would mean that my whole form would have to be coded in PHP then, right? Right now it's in HTML.

[justsomeguy]

PHP produces HTML. Since the information in the form is dynamic it needs to be produced with PHP.

[Titanium]

Thank you. I was able to get it correctly work and for the data to be entered in the database. Just need one or two more parts and I'll be done.Is it possible for when I click on a link called "Add attack" or whatever, that a set of input boxes appear? For example, if I clicked on the link, then the following set of input boxes would appear:

<input type="text" name="d_level1"><input type="text" name="p_level2"><input type="text" name="attack_name"><input type="text" name="accuracy">

But I also want it so that I can click on the link multiple times so that the same set of input boxes would appear right below the first one. The reason I want this is because I am entering characters in a game into a database, and each character has a different number of attacks. How would I go about doing this?

[justsomeguy]

You'll need to use Javascript to add the boxes to the page. It would be best to give each of them a name with brackets so that in PHP they become arrays. So if you have a container for the fields called "test" you can do something like this in java script:

str = "<input type=\"text\" name=\"d_level1[]\">";str += "<input type=\"text\" name=\"p_level2[]\">";str += "<input type=\"text\" name=\"attack_name[]\">";str += "<input type=\"text\" name=\"accuracy[]\">";document.getElementById("test").innerHTML += str;

Then in PHP you would get the arrays and loop through them.

$d_level = $_POST['d_level'];$p_level = $_POST['p_level'];for ($i = 0; $i < count($d_level); $i++){  echo $d_level[$i] . " " . $p_level[$i];}

[Titanium]

str = "<input type=\"text\" name=\"d_level1[]\">";str += "<input type=\"text\" name=\"p_level2[]\">";str += "<input type=\"text\" name=\"attack_name[]\">";str += "<input type=\"text\" name=\"accuracy[]\">";document.getElementById("test").innerHTML += str;

So can I just put that block of code into a .js file and then include it into the file that has the PHP form? Then surround it with <script type="text/javascript"></script> ?

$d_level = $_POST['d_level'];$p_level = $_POST['p_level'];for ($i = 0; $i < count($d_level); $i++){  echo $d_level[$i] . " " . $p_level[$i];}

Does this part go where I want the input boxes to appear?

[justsomeguy]

So can I just put that block of code into a .js file and then include it into the file that has the PHP form? Then surround it with <script type="text/javascript"></script> ?

No, but you can add that code to a Javascript function that runs when you click the link to add a new item. Change the target element ID to match a container you have on the page.

Does this part go where I want the input boxes to appear?

No, the PHP goes in the form processing script.

[Titanium]

Thanks, I was able to get that to work properly. The for statement will allow me to display the results on a page, but how would I get the info entered in a database? Each character of mine is going to have a different set of resistance type and percentage data since a different number of input fields will be used. Right now the javascript is set up like this:

function typeresistance(){str = "Type: <input type="text" name="type_resistance1[]" />";str += "  Percentage: <input type="text" name="type_resistance_percentage2[]" /><br />";document.getElementById("typeresistance").innerHTML += str;}

In another forum, I was taught that database normalization will help. I was taught that a new table called "resistance" should be made, and that the "type" and "percentage" columns should go in it. Then a row called "fire" (an example of the type) and whatever percentage should be placed in that table. I have that set up, but I'm still not sure how I can add the "resistance" data into the database.

[justsomeguy]

The same as the other stuff, if you have both of the fields as arrays then you can loop through the arrays and add each entry.

[Titanium]

I was using the for loop to get the data added into the database, but it's not working. The data isn't being inserted into the database.

$type_resistance = $_POST['type_resistance1'];$type_resistance_percentage = $_POST['type_resistance_percentage2'];for ($i = 0; $i < count($type_resistance) && $i < count($type_resistance_percentage); $i++){mysql_query("INSERT INTO resistance (english_name, $_POST[type_resistance1])  VALUES ('$_POST[english_name], $_POST[type_resistance_percentage2]')");}

In the "resistance" table, I have a column for each different type. What this script here should do is first find the english_name of the character, which can easily be found from one of the form input fields. That value would then be entered in the english_name column one time. Then the percentage value would go with each type.For example, if I had a character called Bob and he was 50% resistance to fire and 25% resistance to water. Bob would be the english_name. The "fire" column would then have the value of 50%. The water column would have a value of 25%. The rest of the types would have a NULL value.

[Synook]

Umm... you need quotation marks or inverted commas around each VALUE in the INSERT statement

mysql_query("INSERT INTO resistance (english_name, $_POST[type_resistance1])  VALUES ('$_POST[english_name]', '$_POST[type_resistance_percentage2]')");}

[justsomeguy]

You also need to refer to the individual array elements, you're trying to insert the entire array.

mysql_query("INSERT INTO resistance (english_name, {$_POST['type_resistance1'][$i]}) VALUES ('{$_POST['english_name']}', '{$_POST['type_resistance_percentage2'][$i]}')");

I'm not sure if english_name is an array or not, but you should get the idea.

[Titanium]

Thanks..I'm trying to get the data to be displayed on a page. Here's what I have so far:

	$type_resistance = $_POST['type_resistance1'];	$type_resistance_percentage = $_POST['type_resistance_percentage2'];	for ($i = 0; $i < count($type_resistance) && $i < count($type_resistance_percentage); $i++)	{	$resistance['0'] = "$type_resistance[$i]";	$resistance['1'] = "$type_resistance_percentage[$i]";	echo "<td align=\"center\" width=\"20%\">	<img src=\"types/" . $resistance['0'] . ".png\" alt=\"\" /><br />"	. $resistance['1'] . "</td>";	}

I know $_POST isn't the right thing to use in this situation because we're not using the form. I think something has to be used to get the values of the things that are not NULL, so if in one row the "fire" and "water" columns are not null and have values, then $type_resistance will display "grass" and "fire" and $type_resistance_percentage will display the values.. I know english_name will have to be defined somewhere, and that can be used by using $_GET because english_name is also displayed in the site URL (index.php?name=english_name).

[justsomeguy]

You'll need to get the data out of the database and display it on the page. It's not going to be an array though, so you're not going to use the same code for it. If you want to display a certain stat then select that specific one from the database, or if the database has a column that associates the stats with users or something then you can use that column to get all of the stats for a user.

[Titanium]

Hello,I was able to get the data displayed on a page. I had to reorganize my tables quite a bit and mess around with some code in order to get it to work. But the reorganization has contradicted with the script that inputs the resistance data into the database from the web form.

$query = "SELECT resistance.id, pokeid.id, types.name, resistance.percentageFROM resistance, typesJOIN pokeidON resistance.id = pokeid.idWHERE pokeid.name = '{$_GET['name']}' AND types.typeid = resistance.typeid";

That's what's being used to select data from the database and displaying it on a web page. Now I need to take whatever is typed in those input fields placed in the database.As you can see from the above script, there are three tables involved: pokeid, resistance, and types. pokeid contains the list of game characters along with an id for each character. types contains a list of the different types in the game along with an id for each type. resistance takes the id from pokeid and the id from types and combines the two along with the percentage. That's how it's able to be displayed on a web page.The web form consists of input fields: Type and Percentage. The script needs to take the value from the Type input field and match it up with the same name in the types table. Then the script takes that name and matches it up with its unique id. There will also be the character's name in the URL (ex: index.php?name=Fox). The script needs to match that name with the same name in the pokeid table. Fox will have it's own unique id. The two ids will then need to be inserted in the resistance table along with its percentage.I hope I can receive some help with writing this script.