turpentyne

Hey there. Not sure how to word this. I've been flying blind as a newbie trying to figure out some pagination for a left joined query. I've got syntax errors trying to set up the SELECT COUNT function that adds up the results of the search on a previous page so it knows how many results matched both tables. Right now, I've got this mess, and it's giving me a syntax error, "You have an error in your SQL syntax; .... near 'LEFT JOIN plantae ON (descriptors.plant_id = plantae.pla' at line 12" $data = "Select (SELECT COUNT(*) FROM descriptors ) AS count1, (SELECT COUNT(*) FROM plantae ) AS count2 LEFT JOIN plantae ON (descriptors.plant_id = plantae.plant_name) WHERE `leaf_shape` LIKE '%$select1%' AND `leaf_venation` LIKE '%$select3%' AND `leaf_margin` LIKE '%$select4%'"; $result = mysql_query ($data); if (!$result) { die("Oops, my query failed. The query is: <br>$data<br>The error is:<br>".mysql_error()); }

With the code I followed in a book, the SELECT COUNT query was just searching one of the tables. It showed a bunch of page numbers at the bottom that didn't go anywhere ( I suspect the numbers matched the entire list of entries in that database) Here's what it was: $data = "SELECT COUNT(*) FROM plantae ORDER BY scientific_name ASC"; So I tried mimicking the LEFT JOIN wording that is further down to see what might happen. Now I get this error: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data/21/2/40/160/2040975/user/2235577/htdocs/leafsearch2a.php on line 40 This is what's on line 40: $row = mysql_fetch_array($result, MYSQL_NUM); Here's the full code: <?php // First connect to database (database connection here) $display = 2; // it's intentionally only 2 for the moment if (isset($_GET['np'])) { $num_pages = $_GET['np']; } else { $data = "SELECT COUNT `descriptors`.* ,`plantae`.* FROM `descriptors` LEFT JOIN `plantae` ON (`descriptors`.`plant_id` = `plantae`.`plant_name`) WHERE `leaf_shape` LIKE '%$select1%' AND `leaf_venation` LIKE '%$select3%' AND `leaf_margin` LIKE '%$select4%'"; $result = mysql_query ($data); $row = mysql_fetch_array($result, MYSQL_NUM); //row 40 above seems to be where a problem is $num_records = $row[0]; if ($num_records > $display) { $num_pages = ceil ($num_records/$display); } else { $num_pages = 1; } } if (isset($_GET['s'])) { $start = $_GET['s']; } else { $start = 0; } if(isset($_POST[submitted])) { // Now collect all info into $item variable $shape = $_POST['select1']; $color = $_POST['select2']; $vein = $_POST['select3']; $margin = $_POST['select4']; // This will take all info from database where row tutorial is $item and collects it into $data variable row 55 $data = mysql_query("SELECT `descriptors`.* ,`plantae`.* FROM `descriptors` LEFT JOIN `plantae` ON (`descriptors`.`plant_id` = `plantae`.`plant_name`) WHERE `leaf_shape` LIKE '%$select1%' AND `leaf_venation` LIKE '%$select3%' AND `leaf_margin` LIKE '%$select4%' ORDER BY `plantae`.`scientific_name` ASC LIMIT $start, $display"); //chs added this in... row 72 echo '<table align="center" cellspacing="0" cellpading-"5"> <tr> <td align="left"><b></b></td> <td align="left"><b></b></td> <td align="left"><b>Leaf margin</b></td> <td align="left"><b>Leaf venation</b></td> </tr> '; //end something chs added in row 81 // This creates a loop which will repeat itself until there are no more rows to select from the database. We getting the field names and storing them in the $row variable. This makes it easier to echo each field. while($row = mysql_fetch_array($data)){ echo '<tr> <td align="left"> <a href="link.php">View plant</a> </td> <td align="left"> <a href="link.php">unknown link</a> </td> <td align="left">' . $row['scientific_name'] . '</td> <td align="left">' . $row['common_name'] . '</td> <td align="left">' . $row['leaf_shape'] . '</td> </tr>'; } echo '</table>'; // row 95 } if ($num_pages > 1) { echo '<br /><p>'; $current_page = ($start/$display) + 1; // row 100 if ($current_page != 1) { echo '<a href="leafsearch2a.php?s=' . ($start - $display) . '&np=;' . $num_pages . '">Previous</a> '; } for ($i = 1; $i <= $num_pages; $i++) { if($i != $current_page) { echo '<a href="leafsearch2a.php?s=' . (($display * ($i - 1))) . '$np=' . $num_pages . '">' . $i . '</a>'; } else { echo $i . ' '; } } // row 112 if ($current_page != $num_pages) { echo '<a href="leafsearch2a.php?s=' . ($start + $display) . '$np=' . $num_pages . '"> Next</a>'; } } //added curly ?>

I need to set up pagination. I've seen a tutorial online, and in a book I have. But they're both for basic queries. I've got a LEFT JOIN. The basic script doesn't seem to work. Is there a different way it has to be done for joins?

I'm trying to figure out how to filter the results of a database query. For example somebody uses a php/javascript form to search for ford cars. When they see 100 results, they then narrow the results by model and/or color. Am I right in thinking that the way to do this is by creating a temporary table of the results? What is the best approach to what I'm trying to do?

ah... those are just from me trimming out the unnecessary stuff to post it here.

Sorry for the delay - been working other projects... Here's a bit more code. It's quite a lengthy page, so I've still let out numerous irrelevant variables and form items. <html> <title>submit a new plant </title> <link rel="stylesheet" type="text/css" href="styles.css"> <body> <?php if (isset($_POST['submitted'])) { $errors = array(); $descriptor1 = trim($_POST['plant_id']); if (empty($_POST['about_this_plant'])) { $errors[] = 'your plant id did not continue to the new page'; } else { $descriptor2 = trim($_POST['about_this_plant']); } $descriptor60 = trim($_POST['green_leaf']); $descriptor61 = trim($_POST['white_leaf']); $descriptor62 = trim($_POST['cream_leaf']); $descriptor63 = trim($_POST['pink_leaf']); $descriptor64 = trim($_POST['red_leaf']); $descriptor65 = trim($_POST['orange_leaf']); $descriptor66 = trim($_POST['yellow_leaf']); $descriptor67 = trim($_POST['blue_leaf']); $descriptor68 = trim($_POST['purple_leaf']); $descriptor69 = trim($_POST['purpleblack_leaf']); if (empty($errors)) { require ('databaseConnectFile.php'); $query = "INSERT INTO table (green_leaf, white_leaf, cream_leaf, pink_leaf, red_leaf, orange_leaf, yellow_leaf, blue_leaf, purple_leaf, purpleblack_leaf) VALUES ('$descriptor60', '$descriptor61', '$descriptor62', '$descriptor63', '$descriptor64', '$descriptor65', '$descriptor66', '$descriptor67', '$descriptor68', '$descriptor69')"; $result = @mysql_query ($query); if ($result) { if(isset($_POST['about_this_plant'])) { $plant_id=mysql_insert_id(); } header ("Location: http://www.mywebsite.com/nextpage.php?var1=$plant_id"); echo 'one plant has been added'; exit(); } else { echo 'system error. No plant added'; echo '<p>' . mysql_error() . '<br><br>query:' . $query . '</p>'; exit(); } mysql_close(); } else { echo 'error. the following error occured <br>'; foreach ($errors as $msg) { echo " - $msg<br>\n"; } } // end of if } // end of main submit conditional ?> <FORM style="border: 1px dotted red; padding: 2px" action="nextpage.php" method="post"><fieldset><legend><b>Describe your plant</b></legend> Leaf color (not including fall season):<br> <input type="checkbox" name="green_leaf" value="<?php if(isset($_POST['green_leaf'])) {echo 'checked="checked"';} ?>" > green <input type="checkbox" name="white_leaf" value="<?php if(isset($_POST['white_leaf'])) {echo 'checked="checked"';} ?>" > white <input type="checkbox" name="cream_leaf" value="<?php if(isset($_POST['cream_leaf'])) {echo 'checked="checked"';} ?>" > cream<br> <input type="checkbox" name="pink_leaf" value="<?php if(isset($_POST['pink_leaf'])) {echo 'checked="checked"';} ?>" > pink <input type="checkbox" name="red_leaf" value="<?php if(isset($_POST['red_leaf'])) {echo 'checked="checked"';} ?>" > red <input type="checkbox" name="orange_leaf" value="<?php if(isset($_POST['orange_leaf'])) {echo 'checked="checked"';} ?>" > orange<br> <input type="checkbox" name="yellow_leaf" value="<?php if(isset($_POST['yellow_leaf'])) {echo 'checked="checked"';} ?>" > yellow <input type="checkbox" name="blue_leaf" value="<?php if(isset($_POST['blue_leaf'])) {echo 'checked="checked"';} ?>" > blue <input type="checkbox" name="purple_leaf" value="<?php if(isset($_POST['purple_leaf'])) {echo 'checked="checked"';} ?>" > purple<br> <input type="checkbox" name="purpleblack_leaf" value="<?php if(isset($_POST['purpleblack_leaf'])) {echo 'checked="checked"';} ?>" > purple-black<br> <input type="hidden" name="submitted" value="TRUE"> <input type="hidden" name="submitted_forward" value='$sn'> <input type="submit" /> </form> </body> </html>

hmmm. Didn't work. I wonder if there's something more I'm missing here? I stripped away all the other database items in the form, and this is essentially what I've got: $descriptor60 = trim($_POST['green_leaf']); $query = "INSERT INTO tablename (green_leaf) VALUES ('$descriptor60')"; <input type="checkbox" name="green_leaf" value="<?php if(isset($_POST['green_leaf'])) {echo 'checked="checked"';} ?>" > in the mysql table, the green_leaf field is: tinyint, set to default of 0 and not null. any thoughts? I've tried a couple variations and still no luck. The checkboxes remain as zeros

I built a series of insertion forms to put entries into a mysql database. Everything is working fine, except that I just now realized my checkboxes don't put any entry in. It's just leaving it's respective smallint field as a default "0". Being new, I've must've overlooked how to handle these. The form's checkboxes have a bit of code in the value that remembers what users chose in case they have to backtrack and redo their form. How would I solve the problem and keep this code? Here's what all the checkboxes are written like: <input type="checkbox" name="green_leaf" value="<?php if(isset($_POST['1'])) echo $_POST['1']; ?>" > green

I have a script that is supposed to change an image source url ('pic2') to match one of several options in a drop down. But it's just adding a folder instead of replacing. I can click on other options in the dropdown, and yet another folder is added. the function: function change3(picName,choice) { var url = (document[picName].src); url = url.replace(/crenate/, choice); url = url.replace(/margin_finelyserrate/, choice); url = url.replace(/margin_lobed/, choice); url = url.replace(/margin_undulate/, choice); url = url.replace(/doubleserrate/, choice); url = url.replace(/leaf_shapes/,"leaf_shapes/" + choice); document[picName].src = url; } So if the one folder says crenate, it needs to be changed to doubleserrate. not say /crenate/doubleserrate/ The dropdown items look like this: <li id="margin1"><a href="javascript:passit4('entire')" onclick="ShowContent('uniquename3'); change3('pic2','crenate');" >crenate or whicher is chosen</a></li>

I forgot to add in the url in its original form, just in case it helps avoid any confusion. It is: <IMG SRC="images/leaf_shapes/1of30images.gif" name="pic2">

Ok. I'm stumped. I have a dropdown from a form that passes a couple variables to a function that changes an image. It works just fine. But I quickly realized that if a user clicks again on the dropdown to change their option, the image link breaks because the url wasn't the same as I'd set up the code for. So I'm trying to put in a couple "else if" conditions to account for those, but it's not working. The url is still wrong. It's adding a folder to the filepath, but not deleting the one it should replace. Hope that made sense. Here's the code: function change3(picName,choice) { var url = (document[picName].src); if (/leaf_shapes\D{1}/.test(url)) { url = url.replace(/leaf_shapes/,"leaf_shapes/" + choice) document[picName].src=(url); } else if (/leaf_shapes\D{1}crenate/.test(url)) { url = url.replace(/leaf_shapes/crenate/,"/leaf_shapes/" + choice) document[picName].src=(url); } In case it helps, here's the dropdown that the information comes from: <div id="megamenu4" class="megamenu"> <div class="column"> <ul> <li id="margin1"><a href="javascript:passit4('entire')" onclick="ShowContent('uniquename3'); change3('pic2','entire');" >Entire</a></li> <li id="margin2"><a href="javascript:passit4('undulate')" onclick="ShowContent('uniquename3'); change3('pic2','margin_undulate;')" >Undulate</a></li> <li id="margin3"><a href="javascript:passit4('crenate')" onclick="ShowContent('uniquename3'); change3('pic2','crenate');" >Crenate</a></li> <li id="margin4"><a href="javascript:passit4('finely dentate')" onclick="ShowContent('uniquename3'); change3('pic2','margin_finelyserrate');" >Finely Serrate</a></li> <li id="margin5"><a href="javascript:passit4('serrate')" onclick="ShowContent('uniquename3'); change3('pic2','serrate');">Serrate</a></li> </ul> </div> </div>

Ah, sorry. When the user uses a form to query the database and pull results, I want to place options for them to narrow the search results by. I've got searches, but I don't know how to make it just search their results, instead of the entire database. For example, their first search is for Red Hyundai cars. Then, if that gives them 100 choices, they can narrow the results by car model. Maybe I'm getting ahead of myself. I was just hoping there was a tutorial somewhere I could reference before bothering the forum with a hundred questions, because I'm so new at this.

Wasn't sure exactly where to post this question: I was wondering if anybody knows where I can find a good tutorial on how to set up a "narrow results" script in php? I'm sure it's pretty easy, but I'm still getting the hang of searches, so I'm looking for anything that helps beat it into my brain.

As a newbie, I've been combining scripts together to create a page that allows viewers to choose from different visual elements of a leaf that will be used to search a database. There are rollovers for each anchor dropdown, and a center image that is in layers to "assemble" the leaf. But I've run into a logistical nightmare and I'm not sure which is the best way to handle this. if variations were slight, i would have no problem. But to show some characteristics I need to know what one or two underlying images were as well. In other words, if I am choosing borders, and I have a round image. The border would have to know to put a round scalloped border - not a square one. (And if somebody goes back to change the shape, I would need the border to automatically know to change to a square scalloped border) Can I build conditionals within the layers? Or does it need to be inline with the image? is there another way to do this before my script becomes a disaster when I try to accommodate for numerous variations. Can I get a snippet or two of code to send me in the right direction? I hope that makes some sense. Here's the page that I'm working on. http://www.mergecreate.com/test25n.html

I've got a test page I've cobbled together from 3 different scripts. It's essentially a dropdown that changes images when its choices are rolled over or clicked, then it applies the choice to a form field that will be hidden in the final script. I'm trying to put in an onblur to the div tag that makes the first image revert to it's default. But it doesn't work. I'm not sure if there's something else I need to do. Here's the page I'm working with: http://www.mergecreate.com/test25i.html

I'm brand new to css and javascript. I've created a div area with the overflow set to auto. Is it possible to create a mouseover scrolling effect for the arrows? If so, how? Or is there another way to achieve the same effect?

I haven't dug into this much but how can I go about making a dropdown image changer, that also changes on rollover as well as select. Basically the dropdown on this page, but that it also changes as you roll over the options: http://www.javascriptkit.com/script/cut173.shtml

That's what I get for typing first thing in the morning, half asleep, eh? And for being a beginner. Thanks for that. I still get an unexpected } error for line 55. When I take one out there, just to see what happens I get: unexpected T_ELSE in /data/21/2/40/160/2040975/user/2235577/htdocs/edit_user1.php on line 55

Am I blind? I don't see the problem with this code. It says: Parse error: syntax error, unexpected '}' in /data/21/2/40/160/2040975/user/2235577/htdocs/edit_user1.php on line 35 <html> <body> <?php if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) { $id = $_GET['id']; } elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) { $id = $_POST['id']; } else { echo 'you have reached this page in error. no variable passed'; exit(); } // that was the part to check for passed variables. This is what does something with it require ('databaseconnect.php'); if (isset($_POST['submitted'])) { $errors = array(); } if (empty($_POST['scientific name'])) { $errors[] = 'you did not enter a scientific name' } else { $sn = escape_data($_POST['scientific_name']); } // now the common name if (empty($_POST['common_name_english'])) { $errors[] = 'you did not enter a common name' } else { $cn = escape_data($_POST['scientific_name_english']); } // now make changes if (empty($errors)) { $query = "UPDATE table SET plant_name='$id', scientific_name='$sn', common_name_english='$cn' WHERE plant_name=$id"; $result = $mysql_query ($query); if (mysql_affected_row() == 1) { echo 'edit a plant<br> The plant has edited' } else { echo 'system error<br> the plant could not be edited due to a system error.'; echo mysql_error() . 'query:' . $query ; exit(); } } else { echo 'error<br> something already insystem or did not work.'; } } else { echo 'error<br>'; foreach ($errors as $msg) { echo " - $msg<br>"; } echo ' please try again'; } // end of if } // endo of submit condition // always show form $query = "SELECT scientific_name, Common_name_english FROM table WHERE plant_name=$id"; $result = $mysql_query ($query); if(mysql_num_rows($result) == 1) { $row mysql fetch_array ($result); echo 'edit a user' <form action="edit_user1.php" method="post"> scientific name: <input type="text" name="scientific_name" size="45" value="' . $row[scientific_name] . '"><br> common name: <input type="text" name="common_name_english" size="45" value="' . $row[common_name_english] . '"><br> <input type="submit" name="submit" value="submit" /><br> <input type="hidden" name="submitted" value="TRUE" /> <input type="hidden" name="id" value="' . $id . '"/> </form>'; } else { echo 'page error'; } mysql_close(); </body> </html>

I've gotten in over my head on this one. I found a dropdown script and some tables for countries and states. I'm using it to enter data into a table. I've put in my own code to put the results into a table - but only if they select everything. What I'm trying to do now is, if they only select country and ignore the last 'states' dropdown, that on submit it enters every state of that country into the database. So, I could pick North America, USA and Florida. That's all it puts into the table: Item ID and Florida's code. But if I stop at USA it puts an entry into the table for all 50 states. This, I can't seem to get working. Here's the code I wrote at the top of the dropdown: // start of my code if( empty($_POST['States']) ) { $descriptor1 = trim($_POST['item_id']); $descriptor2 = trim($_POST['Country']); $query = "SELECT ID FROM States AS ID WHERE List3Ref = ‘$descriptor2'"; $result = @mysql_query($query); if ($result) { foreach ($query as $val ) { $queryb = "INSERT INTO locations_link(item_id, state) VALUES ('$descriptor', '$val')"; mysql_query($queryb) or die( mysql_error() ); // echo $queryb . '<br />'; echo 'locations added for this plant.'; } } else { $descriptor1 = trim($_POST['item_id']); $descriptor2 = trim($_POST['States']); $query = "INSERT INTO locations_link(item_id, state) VALUES ('$descriptor1', '$descriptor2')"; $result = @mysql_query ($query); } } // // end of my code and here's the rest of the code, if it helps spot any error. There's a line, echo '<option value=""></option>'; This seems to be what goes into the space if nobody chooses the dropdown. I've tried playing with this bit too, but to no success. if( isset($_GET['ajax']) ) { //In this if statement switch($_GET['ID']) { case "LBox2": $query = sprintf("SELECT * FROM continent_regions WHERE List1Ref=%d",$_GET['ajax']); break; case "LBox3": $query = sprintf("SELECT * FROM Country WHERE List2Ref=%d",$_GET['ajax']); break; case "LBox4": $query = sprintf("SELECT * FROM States WHERE List3Ref=%d",$_GET['ajax']); break; } $result = mysql_query($query); // this determines what happens if nothing is selected echo '<option value=""></option>'; while ($row = mysql_fetch_assoc($result)) { echo "<option value='{$row['ID']}'>{$row['Name']}</option>\n"; } mysql_close($dbh); exit; //we're finished so exit.. } if (!$result = mysql_query("SELECT * FROM Continents")) { echo "Database is down"; } //for use with my FIRST list box $Continents = ""; while ($row = mysql_fetch_assoc($result)) { $Continents .= "<option value='{$row['ID']}'>{$row['Name']}</option>\n"; } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Simple Dymanic Drop Down</title> <script language="javascript"> function ajaxFunction(ID, Param) { //link to the PHP file your getting the data from //var loaderphp = "register.php"; //i have link to this file var loaderphp = "<?php echo $_SERVER['PHP_SELF'] ?>"; //we don't need to change anymore of this script var xmlHttp; try { // Firefox, Opera 8.0+, Safari xmlHttp=new XMLHttpRequest(); }catch(e){ // Internet Explorer try { xmlHttp=new ActiveXObject("Msxml2.XMLHTTP"); }catch(e){ try { xmlHttp=new ActiveXObject("Microsoft.XMLHTTP"); }catch(e){ alert("Your browser does not support AJAX!"); return false; } } } xmlHttp.onreadystatechange=function() { if(xmlHttp.readyState==4) { //the line below reset the third list box incase list 1 is changed document.getElementById('LBox4').innerHTML = "<option value=''></option>"; //THIS SET THE DAT FROM THE PHP TO THE HTML document.getElementById(ID).innerHTML = xmlHttp.responseText; } } xmlHttp.open("GET", loaderphp+"?ID="+ID+"&ajax="+Param,true); xmlHttp.send(null); } </script> </head> <body> <!-- OK a basic form--> <br><br> <FORM style="border: 1px dotted red; padding: 2px" method="post" enctype="multipart/form-data" name="myForm" target="_self"> <table border="0" width=720> <tr> <td> <!-- OK here we call the ajaxFuntion LBox2 refers to where the returned data will go and the this.value will be the value of the select option --> // I added this box in for an item id passed from elsewhere. The rest is pretty much original script <input type="text" name="item_id" value="<?php $var1 = $_GET['var1']; echo $var1; ?>" /> <br><br> <b>Continent:</b><br> <select name="Continents" id="LBox1" onchange="ajaxFunction('LBox2', this.value);"> <option value='default'></option> <?php echo $Continents; ?> </select> </td> <td><b>Subcontinent:</b><br> <select name="continent_regions" id="LBox2" onchange="ajaxFunction('LBox3', this.value);"> <option value=''></option> <!-- OK the ID of this list box is LBox2 as refered to above --> </select> </td> <td><b>Country:</b><br> <select name="Country" id="LBox3" onchange="ajaxFunction('LBox4', this.value);"> <option value=''></option> <!-- OK the ID of this list box is LBox3 as refered to above --> </select> </td>

aha! that sounds like exactly what I'm trying to do. Awesome. Another lesson learned. thanks.

ah! Now that I've looked into it, that makes it alot simpler. But now that leads me to another question. I didn't mention that I have another variable (from a form) that has to be combined when inserting into the second table. Each row would be: column 1 that comes from the form, and column 2 that comes from the first table. Is there a way to do this using SELECT INTO? I saw some words used like UNION. Does that work to combine a form and a table query?

I quickly wrote this out. Is this close to what I would want to do? $query = ("SELECT column2 FROM table1 WHERE column1 = $variable") $result = mysql_query($query); if (!$result) { while ($row = mysql_fetch_assoc($result)) { INSERT INTO table2 (columnA) VALUES ($row['column2']) }

oh? haha! in other words, I might be overthinking this one. heheh! beginners! Probably because I'm still rough at trying to figure out arrays. it would be several rows. Like searching a table for any column that matches U.S.A.. It would return 50 states.

I'm a newbie just trying to get an idea of whether my idea is possible and how, before I sit down to try writing this code (because it's on a page that's got a heck of alot of code on it already) I want to access a specific table, then pull the info from one column from every row that matches a specific variable, and then use that array to insert information into a second table. I'm getting the sense that WHILE and mysql_fetch_array is how I might do this. Or will it only give me the entire row? Can someone give me just a quick idea how I'd do this so I'm not flying too much in the blind? thanks!