mac_gyver

example of getting a trip pickup amount and quotation amount from the database tables, given a source and destination id - $source_id = 3; // simulate two values that would be from a form submission $dest_id = 2; $source_id = (int)$source_id; $dest_id = (int)$dest_id; if($source_id == $dest_id){ echo "Sorry, the source and destination cannot be the same."; } else { $query = "SELECT loc1.pickup_amt, q.amount FROM locations loc1 join locations loc2 ON loc1.id = $source_id AND loc2.id = $dest_id join quotation q ON (loc1.id = q.location1_id AND loc2.id = q.location2_id) OR (loc2.id = q.location1_id AND loc1.id = q.location2_id)"; $result = $mysqli->query($query) or die("Query failed: $query<br>Error: {$mysqli->error}"); if($result->num_rows < 1){ // either a submitted id was invalid (no location) or there was no matching row in the quotation table echo "Sorry, your search did not match any trip."; } else { $row = $result->fetch_assoc(); print_r($row); // look at what values the query did return } }

your first code didn't show any mysql_error() logic. next you stated you added mysql_error() logic to the two queries and you didn't get any errors. then you posted code in post #9 that formed the query in a php variable, fixing your original sql syntax problem, but broke the quoting around the string values. then you thew that code away (ignoring what ginerjm posted about echoing the query so that you could see what might be wrong with it) and reverted back to the original sql syntax for posts #10 and #12. your original INSERT query syntax is malformed, but because the sql syntax is buried within the php syntax to run the query, it's not easy to see. use the code in post #9, but with the single-quotes around the string data values. edit: basically says what you discovered. you need to always form your queries in php variables (separates the sql syntax from the php syntax, lets you echo/log the actual query, and makes it easier to switch database libraries) and always check for query errors, always.

web servers are not designed to output information on a timed basis. you would need to use javascript and a timer in the browser to display each line of information.

are you even reading what is being stated? the error you are listing is coming from someplace you already had a mysql_error() statement. mindlessly adding more error checking logic (which you should have already had in your code every place you are running a query) won't help find the problem because wherever this is occurring at already had a mysql_error() statement on it. if the code you have been posting is everything on the page, then the problem is in your include file.

given that the error you listed in post #1 came from a mysql_error() output, which the code you have been posting didn't originally contain any mysql_error() statements, the error is coming from some other page/script and not from the posted code.

as i stated in post #8, neither of those queries can produce the error in the first post and given that in post #3 you stated you added or die (mysql_error()); to each query and neither gives an error, those two queries are not where the error is coming from.

why did you remove the single-quotes from around the string data values (post #9)? that query is no longer what you had in post #1 in this thread and will fail every time since the string data is no longer being treated as strings, but as sql keywords and sql syntax. edit: and the query in post #10 isn't the same as post #9. you need to post actual information if you want help with it.

both of the queries you posted couldn't have produced that error. it's coming from somewhere else in your code.

you missed this suggestion - it's your form that is sending the prod_id and prod_price. how else would you know which product the retailer selected and entered his price for?

you would basically need two database tables - a location table (used for picking sources and destinations and with things like a pickup amount) and an quotation table, that holds the quotation price for the valid combinations of sources/destinations. i would start with - location table - id, name, type_id, pickup_amt id - the auto increment id for any location, used when referencing any location name - the display name for the location, "AL1 St. Albans", "Heathrow T1", ... type_id - the type of location (used for categorizing and picking/limiting the selection of locations), Town, City, Airport, Station, ... this should actually be a type_id with a separate table holding the actual text of the types. pickup_amt - any amount to add when the location is the source (you could also have a dropoff_amt column to add when the location is the destination.) the amount is zero for any location that doesn't have any extra cost for a pickup at that location. quotation table - location_id1, location_id2, amount location_id1 and location_id2 - the source and destination ids that are defined in the location table. amount - the quoted amount for the trip between the source and destination. to pick a source and destination, you would simply display (all) the possible locations out of the location table. to limit and narrow down selections, you would have a way of selecting the type (checkboxes) or of searching for specific information (i.e. ajax lookup using the characters entered.) the form would just submit the ids for the source and destination. once the source and destination have been submitted, you would query for the location information and quotation amount using those two ids. this would give you any pickup_amt and the quotation for that trip.

you could create a 'view' in your database for the products table that only 'matches' status != 'Sold' and use the view name in the queries instead of the products table.

version without the while() loop (just the right amount of code and runs very fast) - $time = microtime(TRUE); $mem = memory_get_usage(); $global_array = array(); $day = 60*60*24; $today=date('Y-m-d'); $todayonemonth = date("Y-m-d", strtotime('+1 month')); function date_fm1($val){ return date('d/m/Y', $val); } function date_fm2($val){ return date('w', $val); } foreach ($dates_event['id_event'] as $k=>$v){ $first=$dates_event['date_b'][$k]; if ($first<$today){ $first=$today; } $last=$dates_event['date_f'][$k]; if ($last>$todayonemonth){ $last=$todayonemonth; } $list = range(strtotime($first),strtotime($last),$day); $global_array[$v]['dates'] = array_map('date_fm1',$list); $global_array[$v]['days'] = array_map('date_fm2', $list); } print_r(array('memory' => (memory_get_usage() - $mem) / (1024 * 1024), 'microtime' => microtime(TRUE) - $time)); echo '<br />Number of values in the final global array :' .count($global_array, COUNT_RECURSIVE);

as a continuation of my above reply, i generated 8000 in range random events and this gives a runtime for your posted code around 12 seconds, so if you mean that you have 8000 matching events in a 30 day range (which your post above confirms), i would say this is the base amount of time it is gong to take to expand and fill the dates between the start/end values (you might be able to fractionally reduce this time), but who wants to see or sift through 8000 items at one time. if each event was only one day (many are not) and they were equally distributed, that would be 266 events listed on each day (or for your data, there could be many more than 266 listed on any one day.) it just occurred to me that you can probably use the range() function or a user written function that gets called using array_map() that will avoid the looping entirely to fill the dates between the start/end values.

i was able to produce your 19+ second runtime, using 8000 random events, only when the date strings were missing leading zeros and the comparison basically matched all dates. with correctly formatted dates in my random events, i typically got a 2 second runtime for 8000 events, which changed by only a fraction of a second between 8000 random events and filtering out events that didn't overlap or span the today to one month from today date range. what does your code producing the $dates_event arrays look like or what is a sample of the data in your $dates_event array?

it seems like you are retrieving ALL the rows from your database, then looping through all of them? your query should only match and return rows for events that span today through one month from today, i.e. the event start date or the event end date is between today and one month from today or the event totally spans the date range ( today is between the start date and the end date.) you might be able to make your existing code more efficient by having less code/testing. assuming your query returns only the rows you are interested in, if you simply loop from the date_b to date_f, adding one day inside the loop, this would give you entries for all the days of that event. you could then either ignore the dates outside of today through one month from today (which you are probably doing anyway later in your code when you use the data) or you could remove the date entries outside the date range using a function you write using array_map or array_walk to operate on the entire array at once.

you must use the same exact processing on the password in your login code that you used in the registration code. how else could you possibly compare the stored hash of the password in the database table with the hash of the password that was entered during log in.

your actual code is either making a mysql connection in a different process (file being included via a url) or it is closing the connection or it is making a mysqli connection, ... so that at the time you are trying to run the mysql_query() statement there isn't a valid mysql connection and the mysql_query() statement is attempting to create a connection using defaults (which will fail in most cases.) what's your actual code showing the database connection through to the mysql_query() that is failing?

store the query strings in an array. loop over the array to execute each query using one copy of the sqlsrv_prepare/sqlsrv_execute code.

as to your design, you would have one table that stores the choices, one row per data item - id user_id country_id 1 1 3 2 1 15 3 1 20 4 2 yy 5 2 zz as to the code to take the submitted form data and insert it into a database table - php's original purpose was to process form data and there are literally 100's of thousands of examples of php form to database scripts posted all over the Internet. study some of those examples to see how others have accomplished this task.

the database management tool your web host provides for you to create database users/passwords/databases should list your database hostname somewhere on the page or your host may have a FAQ section that lists the information.

[off-topic] while this doesn't have anything to do with your question in this thread, you need to use alias names in your query. this will simplify, clean up, and reduce the repetition in your queries. you can also include php variables directly in a double-quoted string, thereby further reducing your queries - $query = "select s.class, s.student_id_internal, c.p1contact_no from {$school_id}_students s inner join {$school_id}_contacts c on c.contact_id_internal= s.student_id_internal where class = '$destno'"; you should also form your query statements in a php variable. this helps during debugging (you can echo/log the actual query) and makes switching to a different database library easier, which you need to do since the mysql_ functions have been depreciated starting in php5.5 and new code should be written using the mysqli_ or PDO database libraries. and are you sure you want to have separate tables for each school_id? what happens when a student moves and transfers to a different school or a contact has students in more than one school? you either end up shuffling around data and making sure it got moved correctly or duplicating data between tables. both are bad design practices that can result in data errors and out of sync information.

i would separate the concatenated fields with a delimiter so that combinations of letters between the fields are guaranteed to be unique. billiejohn doe someclass - billiejohndoesomeclass vs billiejohn|doe|someclass billie johndoe someclass - billiejohndoesomeclass vs billie|johndoe|someclass if your rows have an auto-increment id, why not just use the id (identifier) to identify what data has been submitted?

if you are building your original (class) array, on the same page where your form submits, for producing a menu/select list of some type, so that the array exists anyway, then testing the submitted data against the entries in that array as a step in validating the submitted data is no problem. however, if you are building this array just to search through it to test if the submitted value is one of the choices, this is not the way to do it, more so with multiple fields as the distinct query you need to build the menu won't work with multiple fields. retrieving all the values to test if they contain one value is inefficient compared to just testing the values directly in a query.

if the session was still active upto/before the 2 hour limit and the last access time was refreshed/updated when you accessed a page containing a session_start() statement, then that did exactly what you wanted. if that statement means something else, you would need to clarify what it did that wasn't what you wanted. it would also help if you showed the exact settings/code you tried in case there's something wrong with the usage. also, any chance that you executed a session_start() that didn't have the settings present before the session_start() statement, so that the default settings took effect?

any chance the new server has the Suhosin hardened php patch installed? if so, the suhosin.post.max_vars default is 200. edit: also starting in php5.3.9, there is a php.ini setting max_input_vars. default is 1000, but perhaps your server has it set to a lower value.