Search the Community

I am Simplifying my Question here with data as well. *I have a String of keywords that i have to search with my database's fields. *I have 4 fields(variables) upon which i have to apply Where clause. * I wanted to have a query that fetch data either one of 4 where clauses or all of them(OR operation in Where) Here is the Php Code that extracts keywords from string and make query. $condition = strtoupper($_GET['iname']); //for keywords string $operation = $_GET['operation'];//Operation Type AND OR between Keywords //these are four flags determining that which where clause is present $titleflag = "false"; $descriptionflag = "false"; $productoldflag = "false"; $productnewflag = "false"; //if title present if(/*title present or Not*/)//if Not { $titleflag = "false"; $title =''; $titlearray[] = "upper(name) LIKE '%%'"; } else { $title = $condition; $titleflag = "true"; //*************************** $search = strtoupper($title); $search = trim($search); $terms = explode(' ', $search); $count = 0; $line=""; foreach ($terms as $term) { if(($term == " ")) { if($count == 0) { $count++; $titlearray[] = "upper(name) LIKE '%".$term."%'"; } else continue; } else { $titlearray[] = "upper(name) LIKE '%".$term."%'"; } } } //This is it. i did for three other where clauses //description,misold,misnew //below down is my query where i construct the actual query $sql = "Select distinct id,name from item WHERE (".implode( $operation ." ", $titlearray).") AND ".$titleflag." OR (".implode( $operation ." ", $descriptionarray).") AND ". $descriptionflag. " OR (".implode( $operation ." ", $oldmisarray).") AND ". $productoldflag. " OR (".implode( $operation ." ", $newmisarray).") AND ". $productnewflag. " order by name asc"; //Provided Search String /* $condition = "Altera EMP"; */ //If value in $operation = AND then produced query will be look like /* AND OPeration Select distinct id,name from item WHERE (upper(name) LIKE '%ALTERA%' AND upper(name) LIKE '%EMP%') AND true OR (upper(description) LIKE '%ALTERA%' AND upper(description) LIKE '%EMP%') AND true OR (upper(productnoold) LIKE '%%') AND false OR (upper(productnonew) LIKE '%%') AND false order by name asc */ /* Or Operation Select distinct id,name from item WHERE (upper(name) LIKE '%ALTERA%'OR upper(name) LIKE '%EMP%') AND true OR (upper(description) LIKE '%ALTERA%'OR upper(description) LIKE '%EMP%') AND true OR (upper(productnoold) LIKE '%%') AND false OR (upper(productnonew) LIKE '%%') AND false order by name asc */ Now what the requirement is that I must use OR in every Keyword for all Where and hence forth for every keyword. and combine the result of all keyword search either AND or OR

Problem: I want it to only display the vacancy if it's in the location specified. Table "hotel" knows the "location" but table "vacancy" does not (however it does have the same exact hotel name). i have this right now (which only works on table vacancy): "SELECT * FROM vacancy WHERE price > $minprice AND price < $maxprice" ---------------------------below are the mysql table structure and user supplied information-------------------------- Prospective Hotel Guest supplies: Location Max Price/Min Price Minimum Price Quality of Hotel table hotel primary column1 = hotel name column 2 = hotel owner column 3 = location of hotel column 4 = quality of hotel table vacancy (not primary) column 1 = hotel name column 2 = room # column 3 = price

Okay. I am trying to create a form with 7 simple select options and an 8th dual select option. You can see it here: http://rowlandwilliams.com/seize/test.php The idea is that a user can choose any of the 8 options and the total price associated with that selection will populate the box at the top left of the page. The user can also decide to choose a different option than they previously selected. In that case (if they first selected option #2 and changed their mind and chose #5 next), the default value "Please Select" will replace their initial value at option #2. Everything seems to be working fine, except that the option #8 doesn't work at all. I know that's because #8 has two aspects to it, but it has to have that. Any help will be welcome help, since I am a true novice and confused as hell. Thanks.

Hi all, The SQL I'm looking at calculates the the average age within the select and this works fine: (AVG((TO_DAYS(NOW())-TO_DAYS(pl.dob)))/365.242199) AS averageage The problem is when I want to count the ages that are within age groups. The example below should give the total number of ages within the 18-24 group. I can't get this to work, it displays zeros when the select statement is executed, there aren't any sql errors. SUM((case when (TO_DAYS(NOW())-TO_DAYS(pl.dob)/365.242199) BETWEEN 18 AND 24 then 1 else 0 end)) AS '18-24', Can anyone suggest a solution to this? CaptainChainsaw

Hello All, I have a data table with the columns firstname, lastname and rank. I am moving those values from that table into an archive table and the archive table has the same structure, but an additional date on the end. Obviously to tell the different between the repeating values. I have a query like this...... $insert = "INSERT INTO archive (firstname, lastname, rank) VALUES (SELECT firstname, lastname, era FROM old)"; How can I add the mysql command CURDATE() into that insert command? Can I add parentheses inside of the parentheses around the SELECT command?? Thanks in advance??

I have a question... I am trying to figure out how I can search my database for the next available spot open, and if it is not available then the next available in another row. My example is this.... If I have name1, name 2, name3, name4, name5 and the data is joe, jon, tim, null, null Also, this is not always the same... I have many tables like this but some have name1, name 2 while some go up to name10. I need one statement to search any one of these.... How can I do a search that says select the open name in that row. If there are no open rows move to next row and check. I was thinking a loop but not sure how that would work. I appreciate the help! I hope this all makes sense!

I am working on a website with my project mates, in which I need to add a filter dropdown box. When I select one of the option in it, the table being displayed in that webpage will display a different set of data based on the type of data I selected to filter. Also this webpage can only be seen by logging in as a valid user. I am very new to javascript and php programming and after going through several forums, I added the following code as a start to do the filtering, by which the webpage will be able to get the dropdown box option that was selected and display it: <script> function formSubmit() { document.getElementById("report_filter").submit(); } </script> <form method="POST" id="report_filter" action="" > <select name="try" onchange="formSubmit();"> <option value="all">All</option> <option value="Windows">Windows</option> <option value="Win 2008">Win 2008</option> <option value="Win 7">Win 7</option> <option value="Win Vista">Win Vista</option> <option value ="Linux">Linux</option> <option value = "Win 7/Linux">Win 7/Linux</option> </select> </form> <?php if(!(isset($_POST["try"]))) { echo "none"; } else echo $_POST["try"]; ?> The code above works ok when i run that individually in a separate php page, i.e., it ehoes the option that i select in the drop down box. But not so when I add it to the webpage that I am working on, cause when I select an option in the dropdown box the page goes blank and nothing else happens.

I have this code and I have finally got the results in a select drop down box, but each row is in a separate drop down box, not it one with multiple ones. Here is my code: <h1>View Log</h1> <?php $timezone = "America/Chicago"; date_default_timezone_set($timezone); $today = date("m/d/Y"); ?> <form method="get" action="getlog.php"> <table width="1500" border="0"> <tr> <td>Forte ID:</td> <td> <?php $serverName2 = 'SRB-Nick_Desktop\SQLEXPRESS'; $connectionInfo2 = array('Database'=>'cslogs', 'UID'=>'cslogslogin', 'PWD'=>'123456'); $connection2 = sqlsrv_connect($serverName2, $connectionInfo2); $query2 = ' SELECT ForteID FROM Reps'; $result2 = sqlsrv_query($connection2,$query2); if (!$result2) { $message2 = 'ERROR: ' . sqlsrv_errors(); return $message2; } else { $i2 = 0; while ($i2 < sqlsrv_num_rows($result2)) { $meta2 = sqlsrv_fetch($result2, $i2); echo '' . $meta2->name . ''; $i2 = $i2 + 1; } while ( $row2 = sqlsrv_fetch_array( $result2, SQLSRV_FETCH_ASSOC )) { $count2 = count($row2); $y2 = 0; while ($y2 < $count2) { $c_row2 = current($row2); echo '<select name="test" id="test">'; echo '<option name="Forte_ID" id="Forte_ID" value="' .$c_row2. '";>' .$c_row2.'</option>'; next($row2); $y2 = $y2 + 1; } echo '</select>'; } sqlsrv_free_stmt ($result2); } sqlsrv_close( $connection2); ?> </td> </tr> <tr> <td>Start Date:</td> <td><input name="start_date" type="date" value="<?php echo $today;?>" autocomplete="off" required="required"></td> </tr> <tr> <td>End Date:</td> <td><input name="end_date" type="date" value="<?php echo $today;?>" autocomplete="off" required="required"></td> </tr> </table> <br> <input type="submit" name="getLog" value="Get Log"><br><br> <input type="button" value="Back to Form" onclick="window.location.href='index.php';"> <br> </form> Let me know if a picture of what it looks like would help! Thanks in advance!

I am a beginner with MySQL and was wondering what method advanced users would recommend to perform the simple task of looking through two tables to find one row. I don't need code from you, just a general idea of what type of query is best for this situation. If you can offer an explanation of why you like the method, that's even better. Thanks!

I have two columns in a table , first column has a select and the second has a list of checkboxes I'm using smarty to get the number of rows of the table from php , then I use a loop in script code to generate onchange event for every select cell in the table , I'm lost with syntax ! what I want that when I change the select options the checkboxes in the second cloumn become readonly or not . depeanding on the select option which has been chosen . when I open the source of the page I find that there's no code generated in the script at all , it's like the smarty code is ignored need your help to fing the error . the script : <script type="text/javascript"> $(document).ready(function() { <%section name='i' start=0 loop=$rows_number%> $('col_order<%$smarty.section.i.index%>').change(function(){ if( $('col_order<%$smarty.section.i.index%>').val=='first') { $("col_list<%$smarty.section.i.index%>_0").attr('selected','y'); $("col_list<%$smarty.section.i.index%>_0").attr('disabled',false); $("col_list<%$smarty.section.i.index%>_1").attr('selected','y'); $("col_list<%$smarty.section.i.index%>_1").attr('disabled',false); } else if( $('col_order<%$smarty.section.i.index%>').val=='second') { $("col_list<%$smarty.section.i.index%>_0").attr('selected','n'); $("col_list<%$smarty.section.i.index%>_0").attr('disabled',true); $("col_list<%$smarty.section.i.index%>_1").attr('selected','n'); $("col_list<%$smarty.section.i.index%>_1").attr('disabled',true); } }); }); <%/section%> </script>

Hi I am an absolute starter in php and have little to no knowledge on JS, ajax or any other scripting tool. I thought that I will be able to write a whole project based on little php knowledge that I have. But am stuck at one place. I have one select box which contains the list of projects. On the basis of project selected I need to filter out a list of equipment from the equipment table and display it in another select box. This all needs to be done before the Submit. Having searched in google for some help I find that it seems to be simply not possible with php. I need to put in some JS or AJAX code to fulfill this task. Can anybody share with me any sample code or any solutions for this problem. Project and Equipment table are related on Project Code. regards sandy

Hi, I know this is a very basic query but most of the tutorials etc. I can find seem to focus on querying multiple tables rather than one table. I want to filter the data from one table (Mutation) by three variables (prot_name, no_changes and amyloid) (all in this one table). In my main HTML page I have the form: <p><b>Advanced Search:</b>search for subgroups of mutations associated with a particular protein</p> <form action= "adv_mutationsearch.php" method='post'> <select name="result1"> <option value="alpha synuclein">alpha synclein</option> <option value="amyloid precursor protein">amyloid precursor protein</option> <option value="apolipoprotein A-1 precursor">apolipoprotein A-1 precursor</option> <option value="apolipoprotein A-1V precursor">apolipoprotein A-1V precursor</option> </select> <p><b>AND</b></p> <select name="result3"> <option value="amyloid">Amyloid</option> <option value="amorphous">Amorphous</option> <option value="non-amyloid">Non-amyloid</option> <option value="oligomers">Oligomers</option> <option value="undetermined">Undetermined</option> </select> <p><b>AND</b></p> <select name="result2"> <option value="single">Single</option> <option value="double">Double</option> <option value="multiple">Multiple</option> <option value="wild">Wild (original sequence)</option> </select> <br> <br> <input type='submit' value = 'filter'> </form> in my php file: <html><head> <link rel="stylesheet" type="text/css" href="tabs.css" media="screen" /> <title>Amyprot Table Viewer</title></head><body> <?php $db_host = '********'; $db_user = '********'; $db_pwd = '*********'; $database = 'amyprotdb'; $table = 'Mutation'; //receiving results from form submission and allocating to variables for query $query1 = $_POST['result1']; $query2 = $_POST['result2']; $query3 = $_POST['result3']; // connect to database if (!mysql_connect($db_host, $db_user, $db_pwd)) die("Can't connect to database"); if (!mysql_select_db($database)) die("Can't select database"); // query for sorting mutations by protein, number of changes and amyloidogenicity and printing result $result = mysql_query("SELECT * FROM {$table} WHERE prot_name_id={$query1} AND no_changes={$query2} AND amyloid={$query3}"); //to show results in table the current problem lies with the code below, the above code dows not show errors so need to undertsand the below sections. or change it to ordinary print out. $fields_num = mysql_num_fields($result); echo "<h1>Table: {$table}</h1>"; //echo "<table border='1'><tr>"; echo '<table cellpadding="0" cellspacing="1" class="db-table">'; // printing table headers for($i=0; $i<$fields_num; $i++) { $field = mysql_fetch_field($result); echo "<td>{$field->name}</td>"; } echo "</tr>\n"; // printing table rows while($row = mysql_fetch_row($result)) { echo "<tr>"; // $row is array... foreach( .. ) puts every element // of $row to $cell variable foreach($row as $cell) echo "<td>$cell</td>"; echo "</tr>\n"; } //mysql_free_result($result); ?> <div class="search"> <p>Return to database search page <a href="database.php"><span>Search</span></a></p> <p>Return to full mutations table <a href="mutationtable.php"><span>Mutation table</span></a></p> </div> </body></html> The error I get states that $result (when I am trying to print the table of results) is null meaning the whol post submission hasnt worked. I am an early learner as you can see, I have tried multiple ways to do this.. The set up above has been pared right back to the basics as I tried to clean up my code to see where the fault lay and now I am stuck. Thanks in advance x

Hello, I currently have a table that displays a list of elements from a shop. The information comes from a postgreSQL database and is then shown on index.php using a table. The problem I have is I need to add a checkbox to the end of each row so when I select multiple items I can add them to basket. I have the 'select' column set up but no checkboxes as of yet. I need to first implement the checkboxes and then from there I can get and post the selected items into the basket.php page. Any help would be massively appreciated. This is probably something simple im just overseeing. Would rather you also explain than try and give a straight up answer as I am more likely to learn that way. Thank you

$get = mysql_query ('SELECT id , name, description, price , shipping FROM products WHERE id < 100 ORDER BY id DESC'); How do I manage to make it like this, so it order from 1-100 ids, and also check if there is quanity's $get = mysql_query ('SELECT id, name, description, price , shipping FROM others WHERE quantity > 0 ORDER BY id DESC'); Both works, but I want them mixed together, so it reads if there is quanity's And from id < 100 - Thanks

I am using MySQL v5.5.27. I usually would write this SELECT statement like this: SELECT DISTINCT * FROM table WHERE something = 'this OR something = 'that' OR something = 'other' AND thingamajig = 'one' OR thingamajig = 'two' But would this SELECT statement provide the exact same result? SELECT DISTINCT * FROM table WHERE something = ('this OR 'that' OR 'other') AND thingamajig = ('one' OR 'two') I have tried running this and it *seems* to be working. Just want to make sure this second way of doing things won't return errant data in some way that I can't think of. Thanks for any insight, assistance!

Hello. I'm a newbie so sorry if this isn't the best forum to post my problem. I am using a MySQL and PHP to create a web app. I have authentication, and I can register users. I also have a form that users provide information and it is successfully inserting data into a table in my database. I will use fictional fields for my database table called meal_info: username dateStartedDiet numberMealsPerDay costPerMeal Problem: Select user-specific data from the MySQL database, using Session username to select only the current user's data, then display it and do some calculations. Here is thecode at the top, and I am fairly sure it's working: session_start(); //execute commone code require("common.php"); //includes code to connect to database, etc. if(empty($_SESSION['user'])) { // If they are not, we redirect them to the login page. header("Location: login.php"); // Remember that this die statement is absolutely critical. Without it, // people can view your members-only content without logging in. die("Redirecting to login.php"); } Here is the part of the code that has to do with displaying user data: $userID = $_SESSION['user']['username']; //create a variable that is the session username which is identical to the field in our MySQL table $query = "SELECT * FROM meal_info WHERE username = $userID"; //our SELECT statement $result = db->query($query); //execute the query $row_count = $result->num_rows;//count the rows in the table and place in variable to use in incremental loop code for ($i = 0; $i < $row_count; $i++) : $row = $result->fetch_assoc(); //for each row in the table, fetch and create and array $dateStart = $row['dateStartedDiet']; $numberMeals = $row['numberMealsPerDay']; $costMeal = $row['costPerMeal']; echo $dateStart; echo $numberMeals; echo $costMeal;