Jump to content

select table from dropdown menu


Titanium

Recommended Posts

That query looks like it will work, but a regular join or inner join only returns rows that match everything. So if the conditions are missing a row in one of the tables, like there's no resistance row for a certain character, the query won't return anything. In a case like that you would need to use a left or right outer join.

Link to comment
Share on other sites

The query does work, but it is used for taking the data out of the database and displaying it on a web page. However, I don't think the script that adds the data into the database from using the web form will work since I changed most of the table names and the way the data is organized. It's the script with the for loop and the {$_POST['type_resistance1'][$i]}. That script needs to be changed so that it does something similar to the above $query, but I'm not sure what.

$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++){$query = "SELECT resistance.id, pokeid.id, types.typeidFROM resistance, typesJOIN pokeidON resistance.id = pokeid.id AND types.typeid = resistance.typeidWHERE pokeid.name = '$_GET['table']' AND types.name = '$_POST['type_resistance1'][$i]'INSERT INTO resistance (id, typeid, percentage) VALUES ('pokeid.id', '???', $_POST['type_resistance2'][$i])";mysql_query($query,$con);}

That's what I have so far. It's kind of rusty. ??? means that I didn't know what to put there.

Link to comment
Share on other sites

That query isn't going to work, you can't just put an insert statement at the end of a select statement. You need to use an insert statement only, not a select statement. You can use SELECT INTO if you want to insert rows in one table that are coming from another table, but I don't think that's what you're trying to do.I'm not sure what the problem is. You want to insert a record into the resistance table, and it has the columns for id, typeid, and percentage, right? If you're trying to select the ID based on what is in $_POST I guess you could use that, but it would be a lot more usable if you first looked up the ID and were able to give them an error message if they entered something that wasn't found in the database, instead of the insert just failing. Then you would just put the ID you got from the database into the INSERT statement. Otherwise you could use a SELECT statement inside the VALUES clause to select the appropriate ID, but it's going to fail if the SELECT statements don't return any rows.INSERT INTO resistance (id, typeid, percentage) VALUES (SELECT id FROM pokeid WHERE ...., SELECT id FROM types WHERE....,

Link to comment
Share on other sites

hmm something is screwed up with my syntax here:

$query = "INSERT INTO resistance (id, typeid, percentage) VALUES (SELECT id FROM pokeid WHERE name = $_POST[english_name], SELECT typeid FROM types WHERE name = $_POST[type_resistance1][$i], SELECT * resistance WHERE percentage = $_POST[type_resistance_percentage2][$i])";

I get this error message:Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM pokeid WHERE name = Fox, SELECT typeid FROM types WHERE name ' at line 1I've tried putting the single quotes around the $_POST's too and that syntax error keeps popping up.

Link to comment
Share on other sites

Well, you do need quotes, this is string data, so nothing is going to work without quotes. But I don't think the syntax is correct. It makes sense to me to do it that way but I don't think MySQL supports that. It does support this:http://dev.mysql.com/doc/refman/4.1/en/insert-select.html

$query = "INSERT INTO resistance (id, typeid, percentage) SELECT p.id, t.typeid, '" . mysql_real_escape_string($_POST['type_resistance_percentage2'][$i]) . "' FROM pokeid AS p, typeid AS t WHERE p.name = '" . mysql_real_escape_string($_POST['english_name']) . "' AND t.name = '" . mysql_real_escape_string($_POST['type_resistance1'][$i]) . "'";

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...