[SOLVED] multiple SELECTs put into one database field

[dolcezza]

This is what I have using my beginner thought process... can anyone suggest a better/shorter way to do this?

<?php
$authorquery1 = ("SELECT * FROM `authors` WHERE `authorlast`='{$_POST['authorlast1']}'");
$authorresult1=mysql_query($authorquery1);
$info1 = mysql_fetch_array($authorresult1);
$authorid1 = $info1['authorid'];

$authorquery2 = ("SELECT * FROM `authors` WHERE `authorlast`='{$_POST['authorlast2']}'");
$authorresult2=mysql_query($authorquery2);
$info2 = mysql_fetch_array($authorresult2);
$authorid2 = $info2['authorid'];

$authorquery3 = ("SELECT * FROM `authors` WHERE `authorlast`='{$_POST['authorlast3']}'");
$authorresult3=mysql_query($authorquery3);
$info3 = mysql_fetch_array($authorresult3);
$authorid3 = $info3['authorid'];

$authors = array($_POST['authorid1'], $_POST['authorid2'], $_POST['authorid3']);
$commaseparated = implode(',', $authors);
?>

[stuffradio]

You could try using the 'AND' statement or the 'OR' statement.

 

$info1 = mysql_fetch_array(mysql_query("SELECT * FROM `authors` WHERE `authorlast`='{$_POST['authorlast1']}'"));
$authorid1 = $info1['authorid'];

 

That's also one way you could shorten it.

 

[dolcezza]

wouldn't 'AND' or 'OR' get me one result?

To explain a little further...

I have a form entering authors, sometimes only 1, sometimes more.

there is an authors database so the name(authorlast) needs to turn into it's id#(authorid) to be entered into another table like this:  id1, id2, id3

[priti]

try to modified your query like as follow

 

SELECT * FROM `authors` WHERE `authorlast`IN ($_POST['authorlast1'],$_POST['authorlast2'],$_POST['authorlast3']);

 

regards

[dolcezza]

it says unexpected"`" in line 15... that being line 15.

[priti]

TRY

SELECT * FROM authors WHERE authorlast IN ($_POST['authorlast1'],$_POST['authorlast2'],$_POST['authorlast3']);

[cooldude832]

What exactly are you doing

you might want to use a GROUP BY and If but let me know what u trying to do first

[dolcezza]

I have a field in the database called "authors" which is related to another table called "events"

in the events table and author table is the authorid(this is it's unique id)

In the authors table is more info(name, address, city, phone etc..)

Each event can have more than one author... so in the form before this page I have 3 author spots..., but it is entered by the user as the name.

 

So when the form is submitted, it will have up to 3 authors, I need to use the name(authorlast) to retrieve the id(authorid) of all 3 authors, and enter it into an events table(under column authorid) as 3,10,23 (comma delimited?).

 

Does this make sense?

 

 

 

 

 

 

 

 

[cooldude832]

try something

<?php
$author_names_0 = "Jimmy Smith";
$author_names_1 = "Joe FishBob";
$author_names_2 = "Margret Smith";
//Open Sql Connection
$q = "Select AuthorID from `Authors_Table` Where Author_name LIKE '%".$author_name_0."%' ||Author_name LIKE '%".$author_name_1."%' || Author_name LIKE '%".$author_name_2."%'";
$r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);
if(mysql_num_rows($r)>0){
   while($row = mysql_fetch_array($r)){
     $authorsid[] = $row[0];
   }
   $authorsid = implode(",",$authorsid);
   $q2 = "Insert into `"Events_Table` (AuthorID) VALUES('".$authorsid."')";
   $r2 = mysql_query($q2) or die(mysql_error()."<br /><br />".$q2);
}
?>

 

Your logic is a bit confused

if your authors in the events table is always between 1-3 you should define 3 fields to it

then you can get all the info on the event and authors back in 1 query.

 

Secondly you shouldn't let the user type it in if you have a table of data

instead make it be a select that looks like

<select name="author_1">
<option value="1">Bob theMan</option>
<option value="2">Greg Normin</option>
</select>

that would be generated by a mysql query and save you a lot of work.

[dolcezza]

Thanks for your help...

I was thinking of doing a dropdown, however she has over 70 authors, and growing and for now it's only me and the owner entering events and she wants it at least functioning very soon

Is it possible to make a dynamic dropdown using the names in the author table?

 

Also, the reason I put all authors in 1 field is because they never need to be called separately, all those authors will always be with that event. Also it is possible for the amount to increase in the future, what if one day she does an event with 10 authors? I figured it would be easier to modify. Do you still think I should separate it into fields?

 

I am a beginner so all advice is greatly appreciated.

[jitesh]

SELECT * FROM `authors` WHERE `authorlast`IN ('{$_POST['authorlast1']}','{$_POST['authorlast2']}','{$_POST['authorlast3']}');

 

[cooldude832]

separating gets you 1 query vs multiple queries so in my opinion yes.

 

as for the drop down alotta option

you can have a select and a text input

and as they type in the select input it attempts to match the author name "via js or ajax" and when it finds a match it selects that authors name

70 isn't a lot for a select though neither is like 200

 

[priti]

how many drop down you are planning to keep for authors??

 

as you said there could be n number of author related to your event.Two ways you can solve either multi select list or create dynamically new drop down if there are more authors.

 

the query which i gave you works fine at my side i thought you would give a try and let us know weather thats solve the problem or not .

 

don't make change in your script rather cange the query with some author name and try to write that query in your phpmyadmin SQL mode and try to see weather it gives you expected results or not.

 

 

 

 

[dolcezza]

I was actually just reworking it... for those interested, this is what I ended up doing:

$authorlast=$_POST['authorlast'];

$authorids = implode($_POST['authorid'],",");

$query = "INSERT INTO events (date,venueid,authorid,eventcode) VALUES ('$date_for_mysql','$venueid','$authorids','$eventcode')";

 

I am getting the authorid from the dropdown select.

I think this was easier than trying to enter the name, find the id etc...

I am now trying to make a dynamic dropdown and having trouble with that. I changed to a new topic though since it is a different issue

http://www.phpfreaks.com/forums/index.php/topic,176271.0.html

 

Thanks to all!!!