I'm trying to create a document (and a form that can modify or choose the info used in the document) from 2 tables in the same db. The tables are setup like this: Sections table CREATE TABLE sections (section_id int, section_title text, primary key (section_id)) Code (markup): Articles table CREATE TABLE articles (article_id int, section_id int, article_text text, article_title text, primary key (article_id), foreign key (section_id) references sections(section)) Code (markup): Here's some further info on how they are to be connected: sections table is: section_id section_title 1 overview 2 first section 3 second section 4 forth section articles table is: section_id article_title article_text 1 first article This is the first article - it's in the first section 1 second article This is the second article - it's in the first section 2 third article This is the third article - it's in the second section 2 forth article This is the forth article - it's in the second section 3 fifth article This is the fifth article - it's in the third section What I am trying to accomplish is to create a form that will allow me to modify the article title, text and it's section (and it change the articles table), while also allowing me to modify the section title itself (and it modify the sections table). Then, I'm going to create a different form that will allow me to choose the articles to display and then it will compile the article correctly when the form is submitted. (Similar to a standard web form with the checkboxes for the choices, etc..) Presently, I understand that the two tables need to be joined; however, when I try to display the combined info, I have nothing but problems. Here's the coding so far: <? include 'settings.php'; $connection = mysql_connect($mysql_host, $mysql_user, $mysql_password); mysql_select_db($mysql_db, $connection) or die('Can NOT select '. $mysql_db . ' database because ' . mysql_error()); $query = "SELECT s.section_id, a.article_title, a.article_text"; $query .= " FROM articles As a LEFT JOIN sections As s"; $query .= " ON a.section_id = s.section_id"; $query .= " ORDER BY s.section_id, a.article_title, a.article_text"; $result = mysql_query($query, $connection) or die(mysql_error()); ?> Code (markup): I get no errors with my coding; however, how do I check for output? I've tried the simple $row['whatever'] stuff and that does not work. I am a BIG fan of the RTFM approach and I can successfully do an individual table query just fine. Every time I try to modify the query section, I get errors or nothing at all. Am I on the right track with my coding at least? What to do in order to display the data? What about putting them into an array like a normal db? Any assistance you could give me would be GREATLY appreciated. Thanks in advance for the assist! Sincerely, wrstrong
Your SQL is correct as far as I can see. Do you have access to a command line SQL prompt SSH, PHPMYADMIN, etc? I would run this query in there, and see if it gives any output. Also try: $result = mysql_query($query, $connection) or die(mysql_error()); while($array = mysql_fetch_array($result)) { print_r($array); }
I've just added the following to the mix and I DO HAVE OUTPUT! while($array = mysql_fetch_array($result)) { print_r($array); print "<hr>"; } PHP: That's the FIRST time I received the info instead of nothing or an error message! Thanks for that. How do I separate the actual bits of info in the array, though? Once I can get that part, can I just do a simple update instead of select for the query? Thanks for the assist! wrstron
Not sure how you want it displayed, but you can access the array like this: while($array = mysql_fetch_array($result)) { $section_id = $array['section_id']; $article_title = $array['article_title']; $article_text = $array['article_text']; //Do whatever you want here }
<? include 'settings.php'; $connection = mysql_connect($mysql_host, $mysql_user, $mysql_password); mysql_select_db($mysql_db, $connection) or trigger_error('Can NOT select '. $mysql_db . ' database because ' . mysql_error(), E_USER_ERROR); $query = "SELECT s.section_id, s.section_title, a.article_title, a.article_text"; $query .= " FROM articles As a LEFT JOIN sections As s"; $query .= " ON a.section_id = s.section_id"; $query .= " ORDER BY s.section_id, a.article_title, a.article_text"; $result = mysql_query($query, $connection) or trigger_error(mysql_error(), E_USER_ERROR); while($array = mysql_fetch_array($result)) { echo $array['section_id']."<br>"; echo $array['section_title']."<br>"; echo $array['article_title']."<br>"; echo $array['article_text']."<br>"; echo "<hr>"; } ?> PHP: That separates the info for viewing. How do I update/delete the records in this joined table? Thanks for the assist! wrstrong
I have made some serious progress with this project! Here's where I am so far: <? $number = 0; $maxnumber=11; include 'settings.php'; // Include the settings file! $connection = mysql_connect($mysql_host, $mysql_user, $mysql_password); // Connect to the MySQL server! mysql_select_db($mysql_db, $connection) or trigger_error('Can NOT select '. $mysql_db . ' database because ' . mysql_error(), E_USER_ERROR); // Select the db! echo "<center>"; echo "<table width=60%>"; while ($number < $maxnumber) { $query = "SELECT s.section_id, s.section_title, a.article_id, a.article_title, a.article_text"; // MySQL query body! $query .= " FROM articles As a LEFT JOIN sections As s"; $query .= " ON a.section_id = s.section_id"; $query .= " WHERE s.section_id = $number"; $query .= " ORDER BY s.section_id, a.article_title, a.article_text"; $result = mysql_query($query, $connection) or trigger_error(mysql_error(), E_USER_ERROR); // Actual MySQL query! $show_section_title="yes"; while($array = mysql_fetch_array($result)) { if ($show_section_title=="yes") // ONLY show the section title ONCE! { echo "<tr><td><font size=+1 color=black><b><u>".$array['section_title']."</u></b></font></font><br></td></tr>"; $show_section_title="no"; } echo "<tr><td><font size=-1 color=black><u>".$array['article_title']."</u></font></font></td></tr>"; // The article TITLE! echo "<tr><td> <font size=-1 color=black>".$array['article_text']."</font></font><br><br></td></tr>"; // The article TEXT! } $number++; } echo "</table>"; echo "</center>"; ?> PHP: Now, all I have to do is to make a form that utilizes checkboxes to choose which articles to show. I'll keep you posted! Thanks for all of your help thus far! wrstrong
I do have parts of the project working; however, I'm stumped again. Here's the issue:: I am making a page (for editing and adding) that is merely links to the pages that will actually do the modification and/or addition that I need. Here's what I need to do: The article-edit page that I have so far is this... <? $number = $_GET['number']; include 'settings.php'; // Include the settings file! $connection = mysql_connect($mysql_host, $mysql_user, $mysql_password); // Connect to the MySQL server! mysql_select_db($mysql_db, $connection) or trigger_error('Can NOT select '. $mysql_db . ' database because ' . mysql_error(), E_USER_ERROR); // Select the db! $query = "SELECT s.section_id, s.section_title, a.article_id, a.article_title, a.article_text"; $query .= " FROM articles As a LEFT JOIN sections As s"; $query .= " ON a.section_id = s.section_id"; $query .= " WHERE a.article_id = $number"; $query .= " ORDER BY s.section_id, a.article_title, a.article_text"; $result = mysql_query($query, $connection) or trigger_error(mysql_error(), E_USER_ERROR); while($array = mysql_fetch_array($result)) { $section_id = str_replace(' ',' ',$array['section_id']); $section_title = str_replace(' ',' ',$array['section_title']); $article_id = str_replace(' ',' ',$array['article_id']); $article_title = str_replace(' ',' ',$array['article_title']); $article_text = str_replace(' ',' ',$array['article_text']); ?> <form method=post action=article-db-edit.php> <input type=hidden name=new_article_id value=<? echo $article_id; ?>> <table> <tr> <td align=right>Section Title: </td> <td> <input name=new_section_title type=text size=100 tabindex=1 value=<? echo $section_title; ?>> </td> </tr> <tr> <td align=right>Article Title: </td> <td><input name=new_article_title type=text size=100 tabindex=2 value=<? echo $article_title; ?>></td> </tr> <tr> <td align=right>Article Text: </td> <td><textarea name=new_article_text cols=85 rows=5 tabindex=2><? echo $article_text; ?></textarea></td> </tr> <tr> <td colspan=2 align=center><input name=Submit type=submit value=submit><input name=Reset1 type=reset value=reset></td> </tr> </table> </form> <? } ?> PHP: When you pass $number (the article_id number in the articles table,) it displays the proper values that are associated to both tables. Now, what I need to do is to modify that so that the section_id is connected with the appropriate section_title so that if the section itself is modified via this form, the appropriate section_id AND section_title are modified. So far, I've been unsuccessful. Something that at least functions similarly to what I'd need would be to have one dropdown (section_title) that would modify a second dropdown (section_id) within this form. Here's a javascript/html example that would do the modifying the actual dropdown menus; however, how to I integrate it into my script to do what I need? <html> <head> <link rel=stylesheet href="1dynamicMenu.css" type="text/css"> <script language="JAVASCRIPT" src="1js.js"> <!--// //--></script> <script language="javascript" src="BackForwardNew.js"> <!--// //--></script> <meta name="description" content="Using a select to write a select (Double menus)"> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <meta name="GENERATOR" content="Microsoft FrontPage 12.0"> <style type="html/txt"> <!--h2 { color: rgb(128,0,0) } h1 { text-align: center; color: rgb(103,104,74) } --> </style> <!--start of function--> <script language="javascript"> <!--// function ldMenu(mySubject) { var Indx=mySubject; with (document.form2.select2) { document.form2.select2.options.length=0; if (Indx==0) { options[0]=new Option("Pages appear here",""); } if (Indx==1) { options[0]=new Option("Choose a JavaScript Page",""); options[1]=new Option("Alerts","alerts.htm"); options[2]=new Option("Back and Forward Buttons","BackForward.htm"); options[3]=new Option("Contents","index.html"); } if (Indx==2) { options[0]=new Option("Choose an HTML Page",""); options[1]=new Option("HTML Contents","../HTMLGuide/index.html"); options[2]=new Option("Meta Tags","../HTMLGuide/metaTags.htm"); options[3]=new Option("Hyperlinks","../HTMLGuide/ImageHyperlink.htm"); options[4]=new Option("iframes","../HTMLGuide/iframes3.htm"); } if (Indx==3) { options[0]=new Option("Choose a Style Sheets Page",""); options[1]=new Option("Style Sheets Contents","../StyleSheets/index.html"); options[2]=new Option("Fonts","../StyleSheets/fonts.htm"); } form2.select2.options[0].selected=true; } } //--> </script> <!--end of function--> <script language="JavaScript"> <!-- Hide from old browsers //Hide from Java Script function goToPage() { PageIndex2=document.form2.select2.selectedIndex if (document.form2.select2.options[PageIndex2].value != "") { location.href = document.form2.select2.options[PageIndex2].value; } } //--> </script> <meta name="KEYWORDS" content="javascript,menus,form,select,onChange,onChange,new Option,options,location.href,HTML,navigator.appName ,history.go(0),"> <link rel="stylesheet" href="javascript.css" type="text/css"> <title>Using a menu to write a menu</title> </head> <body vlink="green" alink="blue" link="red" background="backg2.gif"> <form name="form1"> <p><select name="select1" onChange="ldMenu(this.selectedIndex);" size="1"> <option value selected>Select a subject </option> <option value>JavaScript </option> <option value>HTML </option> <option value>Style Sheets </option> </select> </p> </form> <form name="form2" method="post" action enctype="text/plain"> <p><select name="select2" onChange="goToPage()" size="1"> <option selected value="Select a page after selecting a subject">Select a page after selecting a subject</option> </select> </p> </form> </body> </html> PHP: Any ideas on how to do this? Thanks for all the help thus far! wrstrong
I have gotten some progress; although, I've hit a few road blocks... lol I've tried to simply use the, 'left join' to actually join the tables; however, I've noticed that sometimes it works, and sometimes it doesn't. When it doesn't work properly, the error that is displayed makes no sense. I have had to use some pretty fancy footwork to get to this point. (Please forgive the crudeness within the coding.) I'm working on a form that will be used to edit the article, it's title and change the section it's in (as needed.) I have everything working EXCEPT getting the section that's connected with the article to be in the drop down box as SELECTED instead of merely an option. Here's where I am at right now::: When you use the actual page, it would be page.php?number=... Where number is the article number you want to change. <? $number = $_GET['number']; include 'settings.php'; // Include settings.php file $connection = mysql_connect("$mysql_host", "$mysql_user", "$mysql_password"); // Connection to the database text mysql_select_db($mysql_db, $connection); // make $mysql_db the current db $res = mysql_query('SELECT section_id, section_title FROM sections', $connection); // Get section_id and section_title from SECTIONS table while($row = mysql_fetch_assoc($res)) // Setup and assign the actual title to an array, 'section[$row['section_id']] { $section[$row['section_id']] = $row['section_title']; } $query = "SELECT s.section_id, s.section_title, a.article_id, a.article_title, a.article_text"; $query .= " FROM articles As a LEFT JOIN sections As s"; $query .= " ON a.section_id = s.section_id"; $query .= " WHERE a.article_id = $number"; $query .= " ORDER BY s.section_id, a.article_title, a.article_text"; $result = mysql_query($query, $connection) or trigger_error(mysql_error(), E_USER_ERROR); while($array = mysql_fetch_array($result)) { $section_id = str_replace(' ',' ',$array['section_id']); $section_title = str_replace(' ',' ',$array['section_title']); $article_id = str_replace(' ',' ',$array['article_id']); $article_title = str_replace(' ',' ',$array['article_title']); $article_text = str_replace(' ',' ',$array['article_text']); ?> <form method=post action=article-db-edit.php> <table> <tr> <td align=right>Section: </td> <td> <select name=new_section_title> <? $res = mysql_query('SELECT section_id, section_title FROM sections', $connection); // Get section_id and section_title from SECTIONS table while($row = mysql_fetch_assoc($res)) // Setup and assign the actual title to an array, 'section[$row['section_id']] { $section[$row['section_id']] = $row['section_title']; if ($section_id = $section[$row['section_id']]) { ?> <option selected><? echo $section[$row['section_id']]; ?></option> <? } else { ?> <option><? echo $section[$row['section_id']]; ?></option> <? } } ?> </select> </td> </tr> <tr> <td align=right>Title: </td> <td><input name=new_article_title type=text size=100 tabindex=2 value=<? echo $article_title; ?>></td> </tr> <tr> <td align=right>Article: </td> <td><textarea name=new_article_text cols=85 rows=5 tabindex=2><? echo $article_text; ?></textarea></td> </tr> <tr> <td colspan=2 align=center><input name=Submit1 type=submit value=submit><input name=Reset1 type=reset value=reset></td> </tr> </table> </form> <? } ?> PHP: ANY help you can give me would be FANTASTIC! Once I get it to pass the appropriate values in the form, I can then go on to edit the appropriate lines in the db tables. Also, if you can think of an easier way to do the form so that the, 'join' can be fully utilized, I'd love to see how it's done. Thanks for ALL of the support you've given me thus far. I've been learning a lot. Sincerely, wrstrong
I've modified the full page somewhat to try and cure the issue; however, I still get the strange error. Here's the full script for that page: (Please note that the article number is just being forced as $number=4. When finished, I'll take that line out and un-comment the $_GET['number'] line above it.) <!-- Get section_id and section_title data from the SECTIONS table (for use in the form) --> <? // $number = $_GET['number']; $number = 4; include 'settings.php'; // Include settings.php file $connection = mysql_connect("$mysql_host", "$mysql_user", "$mysql_password"); // Connection to the database text mysql_select_db($mysql_db, $connection); // make $mysql_db the current db $res = mysql_query('SELECT section_id, section_title FROM sections', $connection); // Get section_id and section_title from SECTIONS table while($row = mysql_fetch_assoc($res)) // Loop through all section_id's { $section_id[$n] = $row['section_id']; // Assign the section_id to $section_id[$n} $section_title[$n] = $row['section_title']; // Assign the section_title to $section_title[$n} } ?> <!-- Get the highest section number (for use in the form) --> <? $query ="SELECT max(section_id) FROM sections"; $result = mysql_query($query, $connection) or trigger_error(mysql_error(),E_USER_ERROR); $row = mysql_fetch_row($result); $max_section_number = $row[0]; ?> <!-- Get section_id, article_id, article_title, article_text from ARTICLES table (for use in form) --> <? $query = "SELECT s.section_id, s.section_title, a.article_id, a.article_title, a.article_text"; $query .= " FROM articles As a LEFT JOIN sections As s"; $query .= " ON a.section_id = s.section_id"; $query .= " WHERE a.article_id = ".$number; $query .= " ORDER BY s.section_id, a.article_title, a.article_text, a.article_id"; $result = mysql_query($query, $connection) or trigger_error(mysql_error(), E_USER_ERROR); while($array = mysql_fetch_array($result)) { $article_section_id = $array['section_id']; $article_section_title = $array['section_title']; $article_title = $array['article_title']; $article_text = $array['article_text']; // echo "<font size=-2 color=red>Section ID: <font color=black size=-2><i>".$article_section_id."</i></font></font><br>"; // echo "<font size=-1 color=red>Section Title: <font color=black><u>".$article_section_title."</u></font></font><br>"; // echo "<font size=-2 color=red>Article ID: <font color=black size=-2><i>".$number."</i></font></font><br>"; // echo "<font size=-1 color=red>Article Title: <font color=black><b><u>".$article_title."</u></b></font></font><br>"; // echo "<font size=-1 color=red>Article Text: <font color=black>".$article_text."</font></font><br>"; // echo "<hr>"; } ?> <!-- The form itself --> <form method=post action=> <table> <tr> <td align=right>Section Title: </td> <td> <select name=new_section_id> <? $res = mysql_query('SELECT section_id, section_title FROM sections', $connection); while($row = mysql_fetch_assoc($res)) { if($row['section_id'] == $section_id) { ?><option selected><? echo $row['section_title']; ?></option><? } else { ?><option><? echo $row['section_title']; ?></option><? } } ?> </select> </td> </tr> <tr> <td align=right>Article Title: </td> <td> <input name=new_article_title type=text size=108 value=<? echo $article_title; ?>> </td> </tr> <tr> <td align=right>Article Text: </td> <td> <textarea name=new_article_text cols=80 rows=6><? echo $article_text; ?></textarea> </td> </tr> <tr> <td colspan=2> </td> </tr> <tr> <td colspan=2 align=center> <input name=submit type=submit value=Submit This Form><input name=reset type=reset value=Reset This Form> </td> </tr> </table> </form> PHP: The section that's to display the drop-down menus are the issue. What's SUPPOSED to happen is IF the section_id from the sections table MATCHES the section_id from the articles table, it's to mark that option as SELECTED. If not, it's just another choice in the drop-down menu. I've tried to do this: <select name=new_section_id> <? $res = mysql_query('SELECT section_id, section_title FROM sections', $connection); while($row = mysql_fetch_assoc($res)) { if($row['section_id'] = $section_id) { ?><option selected><? echo $row['section_title']; ?></option><? } else { ?><option><? echo $row['section_title']; ?></option><? } } ?> </select> PHP: and ONLY the last section is chosen and when I view source, all them are marked as selected. When I try this: <select name=new_section_id> <? $res = mysql_query('SELECT section_id, section_title FROM sections', $connection); while($row = mysql_fetch_assoc($res)) { if($row['section_id'] == $section_id) { ?><option selected><? echo $row['section_title']; ?></option><? } else { ?><option><? echo $row['section_title']; ?></option><? } } ?> </select> PHP: none of them are marked as selected and the first section is displayed in the drop-down menu. Either way, that's not right; however, I can't find where the error is. What I need to have happen is that the section_title that matches the id in the article that's being edited is to be marked selected and the other sections are not marked as selected, but is there in the drop-down menu in case I need to change the section that the article in question is being edited. Can you please double-check the script for me and help me fix this issue? This is the next largest hurdle that I have to get over in order to complete this project. Thank you for all your help so far! Sincerely, wrstrong