Psycho
-
Posts
12,145 -
Joined
-
Last visited
-
Days Won
127
Community Answers
-
Psycho's post in PHP Option Select to MySQL was marked as the answer
Using several of the recommendations above and some of my ideas on improving
<?php $responseMessage = ''; if(isset($_POST['date']) ) { //Get list of ALL submitted dates with valid value $insertValues = array(); foreach($_POST['date'] as $date => $value) { if($value=='A' or $value=='B') { $insertValues[] = "('{$date}', '{$value}')"; } } //Run a a single query to inset all submitted values // *** The query should be a prepared statement to guard against SQL injection $sql = "INSERT INTO weeks (Date, Week) VALUES " . implode(',', $insertValues); if (mysqli_query($link, $sql)) { $responseMessage = "New record created successfully"; } else { // *** For debugging only. Should not display actual errors to user $responseMessage = "SQL: " . $sql . "<br>Error: " . mysqli_error($link); } } // *** Should run query to get already saved values from the database // *** and use those values in the loop below to set any already saved values //Create the form fields for each date $formFields = ''; $weeksOut = 52; for($weekNo=1; $weekNo<=$weeksOut; $weekNo++) { $date = date("Y-m-d", strtotime('Last Monday +'.$weekNo.' week')); $formFields .= "<p>What is this week?: {$date}\n"; $formFields .= "<input type='radio' name='date[{$date}]' value='A'>A</input>\n"; $formFields .= "<input type='radio' name='date[{$date}]' value='B'>B</input></p>\n"; } ?> <html> <body> <?php echo $responseMessage; ?> <br> <form action="test.php" method="post"> <?php echo $formFields; ?> <button type="submit">Submit</button> </form> </body> </html> -
Psycho's post in temporary tables and Unions was marked as the answer
I'm not sure, but you are using the SELECT statement to populate a temporary table. I don't think it is going to return the data. I would think you would then need to run a second query against that temporary table to retrieve the data.
-
Psycho's post in Is a single JSON MySQL retrieval faster than individual rows? was marked as the answer
Also, all the fields used to JOIN the tables should be indexed. Pretty much anything with "id" int he field name in your case.
I was trying to rewrite the query, but without knowing the schema on all the tables it was getting confusing. I *think* your DB design is flawed. The "match_quote_product" appears to be the table to list out the products included in a quote. So, why is there a field for purchase_order_id? Are there different purchase orders for different items int he same quote? There should be a general "purchase_order" table that contains the header data (e.g. date, customer, PO, etc.) Then the details table should include all the items in the quote that reference back to the main record.
There also seem to be some circular references that could be a performance issue as well.
But, here is my attempt at rewriting the query. But, it may not work. I wouodl have to really understand all the relationships to be sure which would take more time than I am willing to invest. And even then, I would probably have to make changes to the schema as well.
SELECT mqp.product_id mqp.price_per_k, mqp.board_feet, mqp.location, mqp.power, mqp.locking_handle_side, mqp.unit_price, mqp.total_price_calculation_id, mqp.quantity, width, length, height, colours.name, total_price FROM match_quote_product mqp JOIN building_products bp ON mqp.product_id = bp.id JOIN match_quote_colour mqc ON mqp.colour_type_id = mqc.colour_type_id AND mqp.quote_id = mqc.quote_id JOIN colours c ON mqc.colour_id = c.id WHERE match_quote_product.quote_id = 50000 AND building_products.purchase_order_id = 5 ORDER BY purchase_order_order ASC -
Psycho's post in Strange variable problem (space problem) was marked as the answer
Parameter values (like the href parameter for an anchor tag) should be within quotes so the browser knows where the value starts and ends. If the value is not within quotes it assumes that the first space is the end of the value
$content .= "<td> <a href='$varcomplete'>test</a> </td>"; -
Psycho's post in Display if user has message was marked as the answer
Yes, I changed some names half way through. As I said - not tested. Add some debugging to validate what IS returned from the query.
Updated code:
<?php //Create an run the query to get unread message count $queryStr = "SELECT COUNT(*) as unreadCount FROM `inbox` WHERE `to` = :username AND `read` = 0"; $inboxQry = $db->prepare($queryStr); $inboxQry->bindParam(":username", $Username); $inboxQry->execute(); //Get the results & create the output $inbox = $inboxQry->fetch(PDO::FETCH_ASSOC); $inboxCountHtml = ($inbox['unreadCount']) ? "{$inbox['unreadCount']} New" : "0"; //Debug lines echo "Query result: "; var_dump($inboxQry); echo "Fetched Row: "; var_dump($inbox); ?> <div class="FooterText"><a href="Inbox.php">Inbox</a> (<?php echo $inboxCountHtml; ?>) -
Psycho's post in detect numeric values make no changes was marked as the answer
//Create function to be called using array_map() function updateDataArray($value) { //Check if any letters exist if(preg_match("#[a-z]#i", $value)) { //Contains a letter replace "-" with space return str_replace("-", " ", $value); } //Does not contain letter, return unchanged value return $value; } $original_data = array( 'abcd-efgh', 'xyz-abc', 'alpha-lima-lima', 'bravo-charlie-charlie', '100-500', '1000-5200', '100000-800000' ); //Call the function above as a parameter in the array_map function $modified_data = array_map('updateDataArray', $original_data); echo "<b>Before</b><pre>" . print_r($original_data, true) . "</pre>"; echo "<b>After</b><pre>" . print_r($modified_data, true) . "</pre>"; Before
Array ( [0] => abcd-efgh [1] => xyz-abc [2] => alpha-lima-lima [3] => bravo-charlie-charlie [4] => B-B-D [5] => 100-500 [6] => 1000-5200 [7] => 100000-800000 ) After
Array ( [0] => abcd efgh [1] => xyz abc [2] => alpha lima lima [3] => bravo charlie charlie [4] => B B D [5] => 100-500 [6] => 1000-5200 [7] => 100000-800000 ) -
Psycho's post in result order by with following was marked as the answer
It's not even valid syntax !<
The WHERE clause is where you include/exclude data based on conditions. The ordering logic will go in the ORDER BY clause. Based on your request and the query I'm not sure if you are trying to exclude records based on the date or if you are wanting the date used for the ordering logic.
I think this may be what you want
SELECT * FROM posts -- First order by the CONDITION of hot_topic = 'yes' AND created in last 7 days -- Secondarily order by the created date ORDER BY (hot_topic ='Yes' AND created > DATE_SUB(NOW(), INTERVAL 7 DAY)) DESC, created DESC LIMIT :per_page OFFSET :offset -
Psycho's post in testing is a column in the database is set was marked as the answer
First, you need to decide what "is set" in the database means. Does it mean the field is NULL, an empty string, or what? Can the values you are pulling be FALSE? If not, have your function return the Boolean FALSE when the value is not set. Then you can do something like this:
if(findit('DEGREETYPE','1moreinfo') !== false){ //Do this } else { //Do that }
Are you actually using the value from the DB or just checking if it "exists" (whatever that means). If you are just checking if the value exists, then have the function return TRUE/FALSE. Also, the !== is only needed if any legitimate values would be interpreted as false. For example, a value of 0 or an empty string would be interpreted as false, do the double equal sign is needed to do a strict comparison. If you won't have any such values in the return data, you could simplify it even more such as this:
if(findit('DEGREETYPE','1moreinfo')){ //Do this } else { //Do that } -
Psycho's post in Searching REST API was marked as the answer
You could pass the search criteria on the query string of the GET method
E.g.
GET /users?name=Joh -
Psycho's post in Column-count weird wrapping issue was marked as the answer
There are two aspects that cause this problem:
1) As the page gets thinner, so do the columns. When that happens the records with the longer names no longer fit into a column. Before you filter the records, if you make the page narrower, the record "Fiona Joy (aka Fiona Joy Hawkins)" won't fit on a single line and has a line break. However, it stays in the same column only because the columns are so long. If it was the record at the end of the column, it could very well be in two columns.
2) Once the page is filtered with just the long records - if the record needs to break across several lines due to a narrow page, the following lines of text will naturally flow to the next column.
Within the artist.css file, find the definition for "#artist_page .artist_full_list a" and add an entry for the "break-inside" property
#artist_page .artist_full_list a { display: block; padding: 4px; break-inside: avoid-column; /* ADD THIS */ /*margin: 0 10px 2px 0;*/ }
That should make sure that the anchor tags do not flow across multiple columns. It worked on a test page from your code, but I didn't try a lot of different configurations in data.
-
Psycho's post in Making buttons the same size was marked as the answer
There is no good solution for this. The browser is doing exactly what it should do. There are some things you can do to achieve what you want, but none are perfect. Here are some things you can do:
1. Use a table. You can try putting the buttons in the TDs and making their height 100%. I tried some variations with buttons and your CSS and didn't get good results. So, you could also use DIVs with an onclick event instead of the buttons
2. Use a fixed height DIV for the text inside the buttons. Use a height that will accommodate the the height needed for the button(s) with the most lines of text. This could be problematic if users have defined larger/smaller text sizes for their displays.
3. Use a fixed height for the buttons that will accommodate the one with the most lines of text. Has the same drawback as #2
-
Psycho's post in Jquery form Help was marked as the answer
It works fine for me in Chrome (not tested in other browsers).
- If the first field is "Yes" the second field is hidden and not required. I can submit the page without errors/warnings.
- If I set the first field to No, then the 2nd field is displayed and IS required. If I attempt to submit w/o making a selection in the 2nd field, the browser displays a warning. If I make a selection in the 2nd field, then I can submit w/o errors/warnings.
Here is my full test page
<html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.2/jquery.min.js"></script> <script> $(document).ready(function (){ $("#JOBPRESENT1a").change(function() { if ($(this).val() == "No") { $("#jobfinish").show(); $("#JOBFINISH1a").prop('required', true); }else{ $("#jobfinish").hide(); $("#JOBFINISH1a").prop('required', false); } }); }); </script> </head> <body> <form method="post" action="" id="theform"> <label id='tooltip1'> Are you currently working with this company?<span></span><a>??<span>INFO</span></a></label><br /><br> <select id='JOBPRESENT1a' name='JOBPRESENT1a'><br> <option value='Yes'>Yes</option> <option value='No'>No</option><label> </select><br> <p id="jobfinish" style="display:none;"> <label id='tooltip1'> Which year did you finish working with this company?<span></span><a>??<span>INFO</span></a></label><br /><br> <select id='JOBFINISH1a' name='JOBFINISH1a'><br> <option value=''>Which Year</option> <option value='2020'>2020</option> <option value='2019'>2019</option> <option value='2018'>2018</option> <option value='2017'>2017</option> <option value='2016'>2016</option> <option value='2015'>2015</option> <option value='2014'>2014</option> <option value='2013'>2013</option> <option value='2012'>2012</option> <option value='2011'>2011</option> <option value='2010'>2010</option> <option value='2009'>2009</option> <option value='2008'>2008</option> <option value='2007'>2007</option> <option value='2006'>2006</option> <option value='2005'>2005</option> <option value='2004'>2004</option> <option value='2003'>2003</option> <option value='2002'>2002</option> <option value='2001'>2001</option> <option value='2000'>2000</option> </select><br> </p> <br> <button type="submit">Submit</button> </form> </body> </html> -
Psycho's post in Help with a query was marked as the answer
You start by talking about two tables (ex order and order_detail), but the query you've provided has neither table listed. So, I'm not sure what you are wanting. Looking at the query you provided, it's difficult to understand since the names are not in English.
The example results you've provided have the same data in all columns except the last. I presume that the columns with the same data are specific to the "order" whereas the last column is the name of the products in the order. If that is the case, then the results are exactly how you would want them to be returned. You don't set up queries to return data constructed for output. That is the job of the code that processes the data. So, if you wanted to create an invoice from that result set, it might look something like this:
//Parse all return data into logical array $reservationsAry = array(); while($row = mysqli_fetch_array($result, MYSQLI_ASSOC) { $reservationID = $row['dreserva']; if(!isset($reservationsAry[$reservationID])) { $reservationsAry[$reservationID]['room'] = $row['sala']; $reservationsAry[$reservationID]['date'] = $row['data']; $reservationsAry[$reservationID]['timeStart'] = $row['inicio']; $reservationsAry[$reservationID]['timeEnd'] = $row['fim']; $reservationsAry[$reservationID]['items'] = array(); } //Add product $reservationsAry[$reservationID]['items'][] = $row['nome']; } //Output the data in appropriate format foreach($reservationsAry as $reservationID => $reservation) { echo "Reservation ID: {$reservationID}<br>\n"; echo "Room: {$reservation['room']}<br>\n"; echo "Date: {$reservation['date']}<br>\n"; echo "Start Time: {$reservation['timeStart']}<br>\n"; echo "End Time: {$reservation['timeEnd']}<br>\n"; echo "Items:<br>\n"; echo "<ul><li>" . implode("</li>\n<li>", $reservation['items']) . "</li></ul>"; echo "<br><br><br>\n"; } -
Psycho's post in Keep togggle action saved on session? was marked as the answer
This works but . . . if the initial state should be to be collapsed there will be a short execution of the collapse functionality on page load
<script> $(document).ready(function (){ //Action for priomary expand/collapse button $(".expshow").click(function(event) { $(this).parent(".expToggle").children("div.info").slideToggle(300); if($(this).text() == '[+]') { $(this).text('[-]'); //Create expand cookie document.cookie = "expand=1;expires=Thu, 31 Dec 2020 12:00:00 UTC"; } else { $(this).text('[+]'); //Delete expand cookie document.cookie = "expand=;"; } }); //Action for secondary collapse button $(".expless").click(function(event) { $(".expshow").click(); }); //On load, check if expand cookie is set. //If not, execute click event to hide div if(document.cookie.indexOf("expand=1") == -1) { $(".expshow").click(); } }); </script> -
Psycho's post in using Double Quotes in Select Query was marked as the answer
Maybe I am not understanding. Are the double quotes supposed to be part of the text to be compared against or are you intending for them to be delimiters?
If the double quotes are meant to be part of the text to be compared against, then you can escape them (using a backslash).
value = odbcconnectSQL("select MATCH(product_group) AGAINST ( '\"+shrimp +(export OR import) \"' IN BOOLEAN MODE) as score from company_info WHERE isin='abcd' AND MATCH(industry, product_group, products, raw_material, business_desc, gisc_ind_2) AGAINST ( '\"+shrimp +(export OR import) \"' IN BOOLEAN MODE) ");
But, if you are wanting to use the double quotes as a delimiter for the string, I'm not understanding why since the single quotes would work just fine.
-
Psycho's post in Join difficulty was marked as the answer
Give this a try. I guessed on the field name 'score' in the select list as the field name from the dart_match_scores table. I would also state that using the match date as the foreign key is a bad idea. I would suggest having a unique identifier in the dart_matches table and using that as the foreign key in any associated tables. As it stands now, a player could not have two matches on the same date. That may be something that shouldn't occur, but the database structure shouldn't be dependent on it.
SELECT date_format(m.Match_date,'%m/%d/%y') as Match_date, m.Team_no, m.Player1, p1.Last_Name as p1ln, p1.First_name as p1fn, s1.score, m.Player2, p2.Last_name as p2ln, p2.First_name as p2fn, s2.score FROM dart_matches m -- JOIN to get player 1 name data JOIN voorhees_data.MMS_Members as p1 ON m.Player1 = p1.Roster_no -- JOIN to get player 1 score data LEFT OUTER JOIN dart_match_scores AS s1 ON m.Player1 = s1.Player_no AND s1.Match_date = m.Match_date -- JOIN to get player 2 name data JOIN voorhees_data.MMS_Members as p2 ON m.Player2 = p2.Roster_no -- JOIN to get player 2 score data LEFT OUTER JOIN dart_match_scores AS s1 ON m.Player2 = s2.Player_no AND s2.Match_date = m.Match_date WHERE m.Match_date = $qdate ORDER BY m.Team_no -
Psycho's post in Is there a way to customize a form action url using jquery? was marked as the answer
You can SUBMIT a form via GET or POST and you cannot change how the data is sent. There is a standard.
However, you could just use the form to redirect to a page based on the values in the form. Create a function that is called when the form is posted and have it take the values and dynamically generate the page to redirect to.
Modified form tag
<form id="search-form" action="/collections/" method="get" onsubmit="return submitAction(this);">
Function to execute when form is submitted. The return false is required to prevent the form from submitting as it normally would. Also note that this is just a bare bones, "example" function. You need to validate that the fields have necessary data before trying to redirect. You should spend some time to complete this.
function submitAction(formObj) { var root = formObj.action; var type = formObj.elements['type'].value; var make = formObj.elements['make'].value; var year = formObj.elements['year'].value; var model = formObj.elements['model'].value; var href = root + type + '/' + make + '+' + year + '+' + model; window.location.href = href; return false; } -
Psycho's post in Warning: mysql_real_escape_string() expects parameter 1 to be string, was marked as the answer
According to the manual for the update method, the second parameter should be an array of name/value pairs. Instead you are passing a name/array. 'meta_value' is supposed to be a field name and $meta_value is assumed to be a value for that field. You are passing an array for for $meta_value. You can't store an array as the value for a field. Not knowing exactly what you are trying to achieve, I can't provide the correct solution.
I'm thinking you should either be storing the data in fields named 'address', 'lat', and 'long' using this
$wpdb->update( $wpdb->postmeta, $meta_value, array( "meta_key" => 'location', "post_id" => $post_id ) );
Or, you may need to convert the array to a string. Definitely not advised to store data in this manner, but since you are using word-press, I'm not sure what your options are
$value = implode(", ", ); $wpdb->update( $wpdb->postmeta, array('meta_value' => $value), array( "meta_key" => 'location', "post_id" => $post_id ) ); -
Psycho's post in Why famous websites use adaptive design ? was marked as the answer
There could be any number of reasons why - many that have nothing to do about technology, but with organization politics and/or division. For example, you may have different groups responsible for the "desktop" vs. "mobile".
Plus, when mobile first became a major factor, responsive design was not at the forefront. It was all about the phone since tablets were still gaining momentum. Thus the goal was to support the desktop and phones - not everything in between. it was quicker to get an adaptive rewrite to production. Also, responsible web design requires a unique skillset for which there was not a huge amount of expertise initially.
Once the adaptive web designs were done to meet the immediate need, there was less of a priority to go back and rewrite once again to be responsive. So, the early mobile adopters may be slower to responsive.
Lastly, many of the companies you cite have specific mobile applications. Many sites try to push mobile users to install their mobile apps instead of expanding their generic web content to be optimal for small displays.
-
Psycho's post in Make table row as column in mysql was marked as the answer
@SamuelLopez:
Are there more than just the two statuses? If not, you should change the field to a Boolean. E.g. name the field "passed" and use 1 (TRUE) to indicate passed and 0 (FALSE) to indicate not passed.
@Barand,
Correct me if I am wrong, but I don't think the IF() statements are needed - just the conditions. A condition will resolve to either TRUE (1) or FALSE (0). So, this should work as well:
SELECT projectid as project , SUM(Status_ID=1) as passed , SUM(Status_ID=2) as failed FROM tbltesttransactions GROUP BY projectid -
Psycho's post in Trying to simplify this if statement was marked as the answer
I gave you a complete, correct answer. But, since you apparently didn't understand it you dismissed it. What you are trying to do (include the URLs in the conditional logic) is a poor implementation. If you ever need to add/edit/remove any of the URLs used for this purpose you would need to modify the logic. Instead, I gave you a solution that allows you to modify the conditions (i.e. the URLs) without ever having to touch the logic. All you would ever need to do is modify the array "$admin_urls" to add/edit/delete any URLs you want used for the conditional check and the logic will work.
And simple does not mean less lines of code - it typically means more lines. I can cram a whole bunch of conditions, loops, whatever onto a single line. That makes it more complicated to read, debug, edit. A simple solution is one that removes complexity. Usually it means each line of code has a specific purpose.
EDIT: The only flaw I see in what I provided was that I read the logic as wanting to see if the session value was in the list of tested URLs. I now see that you wanted to verify it was not in that list of values. Simple enough change
//List of admin urls to test for $admin_urls = array('http://www.golden-wand.com/Pages/admin.php', 'http://www.golden-wand.com/Pages/admin-test.php'); //One simple if() condition if($_SESSION['admin']=='1' && !in_array(strtolower($_SESSION['url']), $admin_urls)) { echo "<input type=\"button\" value=\"Admin Page\" class=\"button hvr-wobble-skew\" onclick=\"location.href='http://www.golden-wand.com/Pages/admin.php'\">\n"; } -
Psycho's post in empty cell in data in columns (display record from database) was marked as the answer
Try this:
<?php //Define the number of columns allowed $max_columns = 3; //Query the data $query = "SELECT ID, FirstName, LastName, MonthEx FROM Members ORDER BY LastName"; $result = mysqli_query($Garydb, $query); //Put results into an array $rows = array(); while($rows[] = mysqli_fetch_array($result, MYSQLI_ASSOC)) {} //Determine the number of rows $max_rows = ceil(count($rows) / $max_columns); //Separate data array into chunks for each column $data_chunks = array_chunk($rows, $max_rows); //Generate HTML output (into a variable) $output = ''; //Iterate over the array chunks while not empty while(count($data_chunks[0])) { //Open new row $output .= "<tr>\n"; //Get next record from each chunk foreach($data_chunks as &$chunk) { //Remove the first element off of the current chunk $data = array_shift($chunk); if(empty($data)) { continue; } //Create the $output .= "<td>{$data['FirstName']} {$data['LastName']}</td>"; $output .= "<td><a href='update.php?update={$data['ID']}'>EDIT</a></td>"; $output .= "<td><a href='delete.php?delete={$data['ID']}'>DELETE</a></td>\n";; } //Close the row $output .= "</tr>\n"; } ?> <html> <head></head> <body> <table border='1'> <?php echo $output; ?> </table> </body> </html> -
Psycho's post in What's wrong with my if not equal statement? was marked as the answer
You only need to include a link to a txt file if you are wanting us to run some specific tests on your site. You do not need it to ask a question about some code.
You may have a failure on both conditions:
if($_SESSION['admin']==='1'){
The three equal signs means it has to match the value and the type - i.e. it has to be a string value of one. A numeric value of 1 will not pass the condition.
On the second condition it will never match because "pages" does not equal "Pages"
-
Psycho's post in How can I get client timezone using PHP/JS was marked as the answer
The timezone offset is basically the timezone. The timezone offset is the difference from the the user's timezone and UTC time. So, you can calculate a time in the user's timezone based on the offset. But, you need to ensure the the times you are storing are in UTC time. Then, when you retrieve the time (from the database) modify it to the user's timezone.
-
Psycho's post in mysql table settings was marked as the answer
You didn't answer my questions, so why should I take the time to answer yours? I don't see how the joined date can be used to know if a user has paid their fees (even though the field doesn't even state that it is a joined date). That date only tells you, presumably, when they joined. So, what data are you storing to know when a user has paid the fees? Are you reducing the fees field when they make payment? That's a poor way to do it since you would have no history of exactly when they were paid and how much they paid.
But, making a HUGE assumption that the fees field is the outstanding fees (which is a terrible process), this query would get you the list of users who have an outstanding fee and their joined date was > 30 days ago.
SELECT id, fname, name, fee, `date` FROM users WHERE fees > 0 AND date < CURDATE() - INTERVAL 30 DAY
Or, if you want ALL the users to display in a list and show specific notices next to the ones who's fee is pending, you could either add that logic to the output processing OR you could add a dynamic field to the query result.
SELECT *, (`date` < CURDATE() - INTERVAL 30 DAY and fees > 0) as fee_due FROM `users`
But, if you are doing as I suspect, it is a poor implementation.