gvp16

Good adivce, thank you.

I have been been coding in php for about 5/6 years. While at uni I was taught the procedural way, and using all the myql_ functions to do database work. Generally speaking if you google for php coding examples you tend to come across examples using those methods which I now believe are out of date. In recent years I have learned some MVC, and used some frame works and open source applications (opencart, wordpress etc...) So im just wondering, these days if you had to write a basic script to get information from a database how would you go about it? or say you were going to make a basic 5 page website with php how would you set it up? The reason im asking these questions is because I want to get out of bad habits and using outdated methods so rather than write something like the following what would you do? $dbh1 = mysql_connect('localhost', 'user', 'password') or die(mysql_error()); //DB connection mysql_select_db('db_name', $dbh1);//choose correct database $query = "SELECT * FROM table WHERE something = something"; $result = mysql_query($query) or die(mysql_error()); $row = mysql_fetch_assoc($result); echo$row["field"]; How would you set up a basic website (content pages,contact form)? In the past I have done something this : request ---> index.php (get page & content from db) ---> pass info to content.php As i said im looking to get out of bad habits and essentially get up to scratch on modern day practices for basic projects, using a framework or CMS for such simple things seems like overkill. Thanks.

I know this question has probably been asked over and over but im interested in getting some opinions in my particular case. I need to develop a system which will be used to generate custom content for a website. There isnt anything over complicated taking place but that doesnt mean there wont be either! Im talking database queries, and some logic. So the question im asking my self is to write a custom application (mvc) or use a frame work. But before everyone starts chanting to use a frame work consider the following points. Reasons to build custom: I am the only developer in the company, and will be for some time. Some of the modules needed will be very bespoke and I need full control over everything. I dont currently know any frameworks and will have to learn one, during this time I could complete half the application (made a small start on it) and have plenty of other things to do. The application would be build for its purpose and nothing else. It will be well documented so should I new developer join the team (or replace me) then along as they have an understanding of php mvc they should get on fine. I would enjoy it to be honest! With that in mind I am fully aware of the benefits of going open source, eg CodeIgniter, cakephp etc... Community help, plugins, lightweight, learn new skills etc... Im just trying to decide what will be the best option here and if anyone else has had a similar situation. Thanks.

Yes, what I have just tried and seems to work well is adding in a "keyword" column to the table, so for example all mountain bike helmets now have mtb helmets as a keyword and so on, the performance is 100% better as well. Thanks for all the advice!

When I do MATCH() AND MATCH() it does run faster however I dont get the results from the products with mtb. I have also tried doing this : MATCH (Product_Name,Cat1,Cat2,Cat3,Colour,Size) AGAINST ('mtb +mountain* +bike* +helmet*' IN BOOLEAN MODE) And removing the other match from the query, but I get the same results as MATCH() AND MATCH(). Thanks.

In what way : like WHERE Product_Name LIKE '%mtb%' AND Product_Name LIKE 'Mountain bike' for example.

Yes I know the LIKE isn't a great idea, some idiot made a mess of the data and put image urls as the product name for some items (this gets updated regularly so If I delete them from the DB they will come back). That aside when removing that from the query it doesn't seem to aid in the performance at all. The problem is I need to return results for both mtb and mountain bike, as depending the type of product there will be occurrences of both. Unfortunately I don't have control over the product titles so I couldn't standardise them either. There is a spellchecker in place as well, so if someone does spell mtb or mountain wrong it is normally corrected. Thanks.

Hi all I have a full text product search query that is built up via php and certain elements are changed based on the search query. The query runs well and the results are very accurate and what we want, however it takes roughly 0.5s - 1.5s to run, as this is used in an ajax search there is a noticeable delay. So im looking for some advice on how I can improve the query. I think I have isolated the problem, some of our products are named slightly different but to the end user are the same thing, so for example an mtb helmet is the same as a mountain bike helmet. As such I wrote in some php to add in the additional keywords when the opposite is being search for. SELECT Product_Name, URL, ImageURL, StockPrice, Brand , MATCH (Product_NAME) AGAINST ('2014') + Rating * 0.1 + views * 0.1 + addedtobasket + MATCH (Product_Name,Cat1,Cat2,Cat3,Colour,Size) AGAINST ('+mtb*' IN BOOLEAN MODE) + #This line here is added when the term mountain is search for MATCH (Product_Name,Cat1,Cat2,Cat3,Colour,Size) AGAINST ('+mountain* +bike* +helmet*' IN BOOLEAN MODE) as Score FROM products WHERE MATCH (Product_Name,Cat1,Cat2,Cat3,Colour,Size) AGAINST ('+mtb*' IN BOOLEAN MODE) + #This line here is added when the term mountain is search for MATCH (Product_Name,Cat1,Cat2,Cat3,Colour,Size) AGAINST ('+mountain* +bike* +helmet*' IN BOOLEAN MODE) AND Cat1 != 'Admin' AND Status = 'True' AND Cat1 NOT LIKE '%prodimg%' AND Stock_Level > 0 AND StockPrice > 0 AND date_updated > '2013-09-26 10:48:03' AND ProductID != '' GROUP BY ProductID ORDER BY Score DESC, Rating DESC When the addition line is added the query takes roughly 0.875s to run, without it comes back as 0.05s there is a large improvement on speed but an impact on the results. I have the following index as well : Any suggestions? Thanks.

Thanks very much, your solution worked for me. The reason I only have one result was because there was only 1 product that matched in the query. Also thanks for the sample query, I would have implemented something similar if the structure was available. Thanks.

ok, you still didn't answer the first part of the question, do you only get that message when removing the IS. Is this a php based application, is error reporting on? or can you turn it on and do you get any errors? the query could be returning null which is resulting the script to fail. This is all an assumption as I dont know anything about the application.

Thanks, the query runs now but I only get one result that seems to match query 1. Can please you give me an example of how you would write the query (if the structure was correct)? Always looking to improve!

are you only getting then that when you remove the "IS" from the IS NOT ? That error message sounds like a server or connection problem rather than an mysql issue.

Hi, thanks for the advice, given the choice that database would have a much more dynamic structure, having only 3 category's is not an issue (this is just a secondary database to a poorly designed system). The basic idea behind this is when I am looking at product 56305, display 4 random products from the database, but with some basic logic applied, eg product 1 is from Helmets, product 2 is of a certain brand and so on... I tried doing what you suggested before posting here, and just tried it again (incase I got it wrong) and I get the following error 1221 - Incorrect usage of UNION and ORDER BY If I remove the order by, I only get 1 result. Thanks.

Whats the error message this time?

Drop the IS and should work.

Hi, I have 4 queries I want to run on the same table which are fairly similar, is there away I can make them into 1 query using joins or sub queries? so for example : I would have : SELECT * FROM products WHERE ProductID != 56305 AND (Cat1 = 'Helmets' OR Cat2 = 'Helmets' OR Cat3 = 'Helmets') AND price_tier = 1 AND (Gender LIKE '%Mens or Unisex%' OR Gender = '') GROUP BY ProductID ORDER BY RAND() LIMIT 1 SELECT * FROM products WHERE ProductID != 56305 AND (Cat1 = 'Forks' OR Cat2 = 'Forks' OR Cat3 = 'Forks') AND price_tier = 1 AND Brand = 'Affix' AND (Gender LIKE '%Mens or Unisex%' OR Gender = '') GROUP BY ProductID ORDER BY RAND() LIMIT 1 and so on for the other 2. This is being using in a php application which will return 4 products, I could easily just run each query separate on the page and get the results, but 4 would mean maintaining 4 separate blocks of code which are pretty much the same, so I was wondering if it could be done in a single query and have just one block of the (the loop for the html). Thanks.

I was hoping to return one row per user with the each day populated. Thanks, I will try Group_concat and report back.

Hi all, I need some help with the following query please, which I am using to consolidate a set of results into a single row via joins, I have done similar queries like this in the past with no problem, but with this query when I am grouping by username or userid the columns are being shown as null SELECT t.user_id, u.username as name, tm.content as monday, tt.content as tuesday, tw.content as wednesday, tth.content as thursday, tf.content as friday, ts.content as saturday, tsu.content as sunday FROM timesheets t LEFT JOIN users u on u.id = t.user_id L EFT JOIN timesheets tm on t.id = tm.id AND WEEKDAY(tm.`date`) = 1 LEFT JOIN timesheets tt on t.id = tt.id AND WEEKDAY(tt.`date`) = 2 LEFT JOIN timesheets tw on t.id = tw.id AND WEEKDAY(tw.`date`) = 3 LEFT JOIN timesheets tth on t.id = tth.id AND WEEKDAY(tth.`date`) = 4 LEFT JOIN timesheets tf on t.id = tf.id AND WEEKDAY(tf.`date`) = 5 LEFT JOIN timesheets ts on t.id = ts.id AND WEEKDAY(ts.`date`) = 6 LEFT JOIN timesheets tsu on t.id = tsu.id AND WEEKDAY(tsu.`date`) = 7 GROUP BY t.user_id See screen shot for example before and after the grouping. Help would be greatly appreciated. Thanks.

hmm, not getting duplicated when processed, so this isnt a issue anymore, feel free to delete this post.

Hi, I have a multidimentional array which looks like this : array(2) { ["HOKIT100E-041"]=> array(3) { [0]=> string(26) "Honda Cr 125-250 2000-2001" [1]=> string(5) "89.99" [2]=> string(1) "1" } ["WAFAXS"]=> array(3) { [0]=> string(27) "Wulf Flite-x Helmet - Green" [1]=> string(5) "65.94" [2]=> string(1) "1" } } howver when I do a foreach on it, the 1st array is being duplicated and I have no idea why this is the code I have : foreach($basketArray as $key => $value) { echo$nvpstr = $nvpstr ."<br/><br/>&L_PAYMENTREQUEST_0_NUMBER$q=".$key." <br/>&L_PAYMENTREQUEST_0_NAME$q=".$value[0]." <br/>&L_PAYMENTREQUEST_0_AMT$q=".$value[1]." <br/>&L_PAYMENTREQUEST_0_QTY$q=".$value[2]; $q++; } this is what is output : Have I made a schoolboy error? Thanks.

lesson learned thanks so much for you help.

WOW, a massive improvement, it now runs about 0.220s rather than 90s! Thanks so much, i didnt spot that i had a duplicate index.

As requested Thanks

ok I will do that shortly, what ive got works fine for what I need, just the problem is its so damn slow when loading up more than a few rows

How would I go about simplifying the query? I havent done alot using joins like this so its all new to me at the moment.