msaz87

My apologies if I'm not using the proper verbiage. Here's an example of a nested loop... I'm sure there's a way to do it with a join or a subquery, but my MySQL experience is limited. Here's the first portion on the page which pulls the details of that form's fields and how many columns were set up: <?php $fields_query = mysql_query(" SELECT field, col_name FROM reg_fields WHERE form_id = '$form_id' AND sortable = '1' ORDER BY rank ASC") or die(mysql_error()); $cn = 1; while($row = mysql_fetch_array($fields_query)) { $col_names[$cn] = $row['col_name']; $cn++ } ?> And here's the portion that actually outputs the results, the first query pulling all the submission IDs and then the nested query pulling each column's result using the submission ID as the key: <?php if(!empty($search_keyword)) { $results_text = " SELECT submission_id FROM reg_form_$form_id WHERE $search_field LIKE '%$search_keyword%' "; } else { $results_text = " SELECT submission_id FROM reg_form_$form_id "; } if(!empty($sort_by)) { $results_text .= "ORDER BY ".$sort_by." "; } else { $results_text .= "ORDER BY submission_id ASC "; } if($submissions_count > $limit) { $results_text .= "LIMIT ".$start_at.", ".$limit; } $results_query = mysql_query($results_text) or die(mysql_error()); $rowclass = 0; while($row = mysql_fetch_array($results_query)) { $submission_id = $row['submission_id']; ?> <tr class="row<?php echo $rowclass ?>"> <td><?php echo $submission_id ?></td> <?php foreach($col_names as $key => $value) { ?> <?php $data_query = mysql_query(" SELECT $value FROM reg_form_$form_id WHERE submission_id = '$submission_id'") or die(mysql_error()); while($row = mysql_fetch_array($data_query)) { $data = stripslashes($row[$value]); } ?> <td><?php echo $data ?></td> <?php } ?> </tr> <?php $rowclass = 1 - $rowclass; } ?> Thanks for the help

Sorry, I thought there might be a general answer to that practice... but I'll elaborate: My site will offer users the ability to construct forms and then manipulate the results. So these forms can have as many fields as they want and however many results they want. Then this will scale with however many users sign up for it. In the code right now, I have occasions where a MySQL loop is running and within that loop, another MySQL query is executed... for instance there's a function to copy a form they have already set up, so the loop runs through a list of fields and the query within that loop INSERTS that data into the new field's table. Other spots, there's a MySQL loop within another MySQL loop... such as when viewing the results of a form. The first loop calls all the fields for that form, then the loop within calls the data inputted for said field. My concern is whether this practice will put too much strain on the database when scaled. Right now, it works fine, but if there's 10, 20, 100 users accessing it, will it be too bogged down? Hopefully that makes more sense... thanks for the help

Best practices question: Is it bad for scalability to have a query (say an UPDATE or an INSERT) within a loop -- or is it only bad for scalability to have one loop running within another? Are there any occasions when this is acceptable? Thanks for the guidance

Figured it out.. $results_fields = join(", ",$fields); $results_query = mysql_query(" SELECT $results_fields FROM reg_form_$form_id WHERE submission_id IN ( SELECT submission_id FROM reg_form_$form_id ORDER BY submission_id ASC)") or die(mysql_error()); while($row = mysql_fetch_array($results_query)) {

Hey all, I'm trying to eliminate a spot where I used a query within another query's loop, but to do so I'm a little lost on creating the combo query. The current setup is as follows: <?php $results_query = mysql_query(" SELECT submission_id FROM reg_form_$form_id ORDER BY submission_id ASC") or die(mysql_error()); while($row = mysql_fetch_array($results_query)) { $submission_id = $row['submission_id']; ?> <tr> <?php foreach($fields as $col_name) { ?> <?php $data_query = mysql_query(" SELECT $col_name FROM reg_form_$form_id WHERE submission_id = '$submission_id'") or die(mysql_error()); while($row = mysql_fetch_array($data_query)) { $data = stripslashes($row[$col_name]); ?> <td><?php echo $data ?></td> <?php } ?> </tr> <?php } ?> The $fields variable is defined earlier and specifies what columns of the database it's supposed to pull, so the first query pulls the individual submission ID, then the second one pulls all the corresponding data for said ID. Any help combining these would be greatly appreciated... thanks in advance

You never define the commenter's avatar. It's only defined once, way above the comment section... so if anything, that's going to be the only avatar displayed for all the comments, since it's never changed in the loop.

There's not enough information to go by in the code you provided. Where is $avatar defined?

You could put the checked IDs into an array before you output the same list of checkboxes, then have something that compares the ID of that checkbox in the loop to see if it's in the array and if it is, to echo the "checked" bit

It doesn't like the format of the date you're storing... I had to test it to make sure. Your two options are to store the date in a different way (e.g. YYYY-MM-DD) or you can use another solution like just rearranging the numbers in the string. $first = substr($showdata['date'], 4, 2); $second = substr($showdata['date'], 2, 2); $third = substr($showdata['date'], 0, 2); $newDate = $first.$second.$third;

$date = date("dmy",strtotime($showdata['date']));

It's fairly simple... when you receive the checkboxes to begin with, I assume you store whether they're checked or not with their ID number... so when you go back to output them, do something like this: $query = mysql_query(" SELECT * FROM categories WHERE something = 'something' ORDER BY whatever ASC") or die(mysql_error()); while($row = mysql_fetch_array($query)) { if($row['checked'] == 1) $checked = "checked"; echo "<input type='checkbox' name='whatever' id='whatever' ".$checked." >"; } So basically if that ID has the 1 value, or whatever you want in the checked column, it'll include the "checked" in the input tag and it'll be checked on output.

Well assuming they line up (e.g. the first value in myInput goes with the first value in myInput2) you could do something like: $count = 0; foreach ($myInput as $eachInput ) { echo $eachInput ."<br>"; echo $myInput2[$count]; $count++; }

Instead of: $rowtag = mysql_fetch_array($sqltag); Do this: while($rowtag = mysql_fetch_array($sqltag)) { echo $rowtag['tag']."<br/>"; }

$var = $_GET['code'];

You could, but you don't have to. You would just have your products table where you grab the ID, then a row for the tags. So the same product ID would show up multiple times for however many tags you had and when you went to delete a tag, it'd query by the product ID and tag = 'tag-to-delete'

Show code

That was what was wrong... only I fixed it in a much more difficult way. I realized the problem was that the query had to be actually in the URL, so I had the form post to a simple redirect page that then rebuilt the search link with the query in it. The whole _GET, _POST, _REQUEST thing is still a little foreign to me, but your solution definitely makes sense and seems to be the way to go. Thanks!

To do it dynamically you'd need to use javascript. If you want to do it with PHP you can do something like this: the form: <form method="post" action="nextPage.php"> <input type="checkbox" name="optionA"> Option A <input type="checkbox" name="optionB"> Option B <input type="checkbox" name="optionC"> Option C </form> the receiving page: <?php $optionA = $_REQUEST['optionA']; $optionB = $_REQUEST['optionB']; $optionC = $_REQUEST['optionC']; if($optionA == "on") { ?> your HTML elements for Option A here <?php } elseif($optionB == "on") { ?> option B <?php } elseif($optionC == "on") { ?> option C <?php } else { ?> if none are selected <?php } ?> The above will change if you need to factor in multiple selections, etc. but that's the basics of it

Try plugging in the hard value to test it. $head_query = mysql_query("SELECT * FROM article_cats WHERE id=1") or die(mysql_error());

Where is $page being defined? Well you could check to make sure your queries are populating properly by splitting out the actual statement like this: if(empty($article)){ $head_query = "SELECT * FROM article_cats WHERE id='$page'"; echo $head_query; //$head_query_run = mysql_query($head_query) or die(mysql_error()); } This way you can at least make sure the query is being put together properly

In the host update did they modify the DB structure at all? Maybe the table name changed if the script was working before the update and you haven't touched it since

Hey all, I'm pretty sure this is a very easy solution, I've just been staring at it too long and am probably missing the cause. I'm using Google's site search API and using their "Results Only" setup, so I design my own search box, then just drop their code into the results page and specify the variable the query is being passed as. So here's my search box: <form method="post" action="/search-results/?q="> <input id="searchTxt" type="text" maxlength="128" name="q" size="15" value="Search" onfocus="if (this.value == &#039;Search&#039;) {this.value = &#039;&#039;;}" onblur="if (this.value == &#039;&#039;) {this.value = &#039;Search&#039;;}" /> <input type="image" name="op" value="Search" src="/wp-content/uploads/2011/07/search-button-magnifier.png" /> And the results code via Google: <div id="cse" style="width: 100%;">Loading</div> <script src="http://www.google.com/jsapi" type="text/javascript"></script> <script type="text/javascript"> function parseQueryFromUrl () { var queryParamName = "q"; var search = window.location.search.substr(1); var parts = search.split('&'); for (var i = 0; i < parts.length; i++) { var keyvaluepair = parts[i].split('='); if (decodeURIComponent(keyvaluepair[0]) == queryParamName) { return decodeURIComponent(keyvaluepair[1].replace(/\+/g, ' ')); } } return ''; } google.load('search', '1', {language : 'en'}); google.setOnLoadCallback(function() { var customSearchControl = new google.search.CustomSearchControl('XXXXXXXXXXXXXXXXXXX'); customSearchControl.setResultSetSize(google.search.Search.FILTERED_CSE_RESULTSET); var options = new google.search.DrawOptions(); options.enableSearchResultsOnly(); customSearchControl.draw('cse', options); var queryFromUrl = parseQueryFromUrl(); if (queryFromUrl) { customSearchControl.execute(queryFromUrl); } }, true); </script> <link rel="stylesheet" href="http://www.google.com/cse/style/look/default.css" type="text/css" /> But nothing seemingly the query is not sent with the user to the results page. Manually adding "?q=test" shows the results work. Am I missing something here? I'm using Wordpress on this site, so not sure if the permalink setup is screwing with it at all. Thanks for the help

HTML page: <html> <body> <form action="redirect.php" method="post"> Link: <input type="text" name="id" /> <input type="submit" /> </form> </body> </html> PHP page: <?php $user_id = $_REQUEST['id']; header('Location: http://www.example.com/users/'.$user_id.'/'); ?> Your redirect page only needs PHP -- if you have any HTML in it, it has to be below the header redirect, or it'll fail

Check out readdir. You can use that and opendir to store them all in an array and pull them out however you'd like.