glendango

Thanks as always for your time and answers. I will use that table for users to see the breakdown.. cool

i get it...its adding the values up and dividing them... what if i created a new status for the user which i called '2'. it wouldnt work then would it. i.e. in my case 0 = no sale 1= sale 2 = completed sale this works i think SELECT AVG(Status=1) FROM firsts where usr_id='1'

Sorry , just read this thread again ..thanks to all who responded. i used a meta refresh in the end so i know the user has gone back to the login page ..chrome does a good job of retaining the log in details so users can get straight back in.

what the.. ? how is that even working without telling mysql to count only the rows with '1' in them ?

Hi. Looking to get a % from my table(firsts) to give user stats.. I have : Date_made Status Date 1 0 Date 2 0 Date 3 1 Date 4 0 The answer will be 25% because their is 1 instance of '1' divided by 4 dates = 25% ( i know you can do sums so sorry for basic explain. ) i have tried below query and get an error but it best explains what iam trying to do: SELECT count(status) ,count(date_made) , status / date_made *100 from firsts where status = '1' i ve tried for a few hours but i just get back answer of 100 or 7x100 = 700 etc.. sorry i get back 700 in my real app becuase their are 7 instances of '1'

what does this site use to keep user logged in??? iam going to to take them back to log -in page after 10 minutes...

yep as always..looked at php.net and ended up 10x more confused.. i use the dateTime stamp to create the entry into the database and so dont control the 'time'. Does that make more sense? Do i change the ini or do i script it? Am i right about the godaddy part as well? this seems stupid seeing as they must have so many users in europe / uk .

Also you mentioned shared hosting which i have for now . When a user adds a lead into the database it records it -7 hours gmt ( i.e. East Coast USA where the server is based) Is this ok or shoudl i be changing this in the .ini code as well to get it back to gmt time. Or is this just crap godaddy and i should use a european server?

I realise this might be a html answer but i expect there is a better php solution. My issue is keeping users logged into my app. (securly) this below simply refreshes the page every 200 seconds to keep the session live. <meta http-equiv="refresh" content="200;url=http://mywebsite/login.php" /> What i dont know (or cant find anywhere) is best practice. If a user is idol for say 10 minutes what should happen? Do i keep refreshing the page. ( this seems a bit clumsy as if after 190 seconds the user starts to complete a form..the page will still refresh. What do you guys advise i do? is this jquery, html or php solution? Ultimately i want a happy customer who doesn't come back to a site that's logged them out but doesn't tell them. ( at moment my app looks like ( with out the meta code) they are logged in til they press another button. Then the staus changes to 'logged out' but they might not notice this and waste time filling out a form with no session

thx , I thought so but needed to confirm for my own mind.

Hi.. I have a database which works fine. I ve just started using google charts to make the results look better for the U.Experience. . The example tables are very basic like: Country Visits England 4 Usa 6 Brazil 3 I get all my results form where statements so I have no tables which actually count the entries and show them in a basic table like this. My question is : Should I be using where, count() etc statements to populate the charts or should I have mini tables in my schema that present the information neatly like the above or a counter next to the id of the user/country etc e.g. so I don't need to worry about statements in the chart code.... thx for reading.

oh, thanks.. i love a bit of cryptic code mixed in with my code. Azes ,, lol i deserve it.

lol cheers handball , i mean ginerjm... whats going on? i cut and paste into your text field from sublime and that's how it saves. i get undefined variablle for pdo Barend.

hi handball,, i just worked through what you sent over..it echos same as i already have. what are you saying with the red text.. 'put code here' or are you commenting it out..,, i took as commenting out. barend i see what your doing. i did have a go at trying to slect in 1 statement but could nt get the results due to the statement always seeming to mix the two f.date_made columns up ( not this time but last week when i tried) ..but i will try again... this has to be the way forward as i love result based apps. '

Thanks so much for the answers. Wanted to reply before i work through it all. Its literal lack of knowledge Barand not an unwillingness. But i totally get your point. Many thanks. Iam near the end of my prototype now anyway.. no way i can learn everything on my own including how to make it safe on a live site.... so hopefully my questions will stop soon...until my next app!!!!!!!

Hi, ive been trying to display 2 columns side by side with 2 select statements.. please see attached for results.... for some reason the 2nd select fetches the database values correctly but echo's them horizontally and not vertically like the first columns set of results. Branch Name Total Ever Total Year Barry Rich Williams 13 12 5 4 0 0 0 0 Brum Lisa Williams 5 London Stephen Eckley 4 Mythr Debs Lovelock 0 Basing Derek Barti 0 Devon Barry Jims 0 Glasgow James bonds 0 Total 22 21 <table class="table table-striped table-bordered table-hover" style="width: auto; margin: " id="example" cellspacing="0" > <!--, table table-inverse table-bordered --> <thead > <tr > <th>Branch</th> <th>Name</th> <th>Total Ever</th> <th>Total Year</th> <!-- <th>Total Year</th> --> </tr> </thead> <tbody > <?php //TOTAL EVER original $result = mysqli_query($conn,"SELECT u.company_id, u.branch, u.name ,u.surname, u.id, count(f.date_made) as num_rows FROM users u LEFT JOIN firsts f ON u.id = f.usr_id where u.company_id='".$_SESSION['company_id']. "' group by u.id order by num_rows DESC limit 10 ") ; $result1 = mysqli_query($conn,"SELECT u.company_id, u.branch,u.name,u.surname, u.id, count(f.date_made) as date_mades FROM users u LEFT JOIN firsts f ON u.id = f.usr_id AND DATE(f.date_made) and year(curdate()) = year(date_made) where u.company_id='".$_SESSION['company_id']."' group by u.id order by date_mades DESC limit 10 "); while($firsts=mysqli_fetch_array($result)){ echo "<tr>"; echo"<td>".$firsts['branch']."</td>"; echo"<td>".$firsts['name']. ' '.$firsts['surname']."</td>"; echo"<td>".$firsts['num_rows']."</td>"; while($firsts=mysqli_fetch_array($result1)){ echo"<td>".$firsts['date_mades']."</td>"; } } $result = mysqli_query($conn, "SELECT count(f.date_made) as date_madesss FROM users u LEFT JOIN firsts f ON u.id = f.usr_id where u.company_id='".$_SESSION['company_id']."' "); while($firsts=mysqli_fetch_array($result)){ echo "<tr>"; echo"<td>".'Total'."</td>"; echo"<td>"; //echo"<td>"; echo"<td>".$firsts['date_madesss']."</td>"; //echo"<td>".$firsts['date_madess']."</td>"; //TOTAL YEAR $result = mysqli_query($conn, "SELECT count(f.date_made) as date_mader FROM users u LEFT JOIN firsts f ON u.id = f.usr_id WHERE year(curdate()) = year(date_made) and u.company_id='".$_SESSION['company_id']."' "); while($firsts=mysqli_fetch_array($result)){ // echo "<tr>"; //echo"<td>".'Total'."</td>"; //echo"<td>".''."</td>"; //echo"<td>".''."</td>"; echo"<td>".$firsts['date_mader']."</td>"; } } ?> </tbody> </table> </div>

this works well - gives me numbers by the side of users : set @rownum := 0; select usr_id, count(date_made) as date , @rownum := @rownum + 1 as row_number from firsts group by usr_id when i add 'order by date' at the end of the select, the numbers follow the usr_id around and don't stay in sequence i.e 1,2,3,4,5 this is what all answers do on stack as well... .

thats good to hear! i will only blueprint mine then get it written properly. i need to work through your code tomoorow when awake but the bit ive done : SELECT usr_id ,count(date_made)as da, @seq := @seq + 1 as seq FROM firsts JOIN (SELECT @seq:=0, @rank:=0, @prev:=0 ) as initialize GROUP BY usr_id order by da DESC does this... the seq seems to be acting like row_num which keeps latching onto the row number rather then just give me a straight count. da in this case is the amount of leads,,, so rather then 1,2,6,3,5,4 i want 1,2,3,4,5,6 usr_id da seq 1 12 1 3 5 2 9 4 6 5 2 3 7 2 5 6 1 4 this is the real select i am working with: on main app if it helps : $result = mysqli_query($conn, "SELECT u.name , u.surname, f.usr_id, COUNT(f.usr_id) AS totalfirstsever FROM users u JOIN firsts f on u.id = f.usr_id group by f.usr_id order by totalfirstsever DESC limit 10 ");

SELECT usr_id , @seq := @seq + 1 as seq FROM firsts JOIN (SELECT @seq:=0, @rank:=0, @prev:=0 ) as initialize GROUP BY usr_id DESC ​ this is great... simplified it to spit out the sequence on MySQL for just the sequence.cheers.... i changed to group as well so it kept all users to 1 entry. no wi will work on the rank part . another annoying question for you... every time i try an do something new on my app it takes 1 day and at the end of the day i ask for help and you help me....this cant be correct... ( bearing in mind iam not a dev and only been coding a few months..) i know the answer to this is to employ a pro app creater , but what do they use?? do they actually sit there all day working this stuff out for their custoemrs or does symphony, laraval frameworks etc do it all for you??? i cant see how they would do it all for you as iam asking for quite specific things to make my app niche.. would love some inside info as to best practice // give up ( which i wont lol)

yes very much so... the rank would be another level if you want to save me another day of stack overflow pain.

i will go for the increment of 1 answer for now...rankings is just a dream at moment

barand knows about my lack of memory and stupidity.... can i have more of a clue please barand has posted the result with no code... he is getting angrier every day..

that is desired output,,,numbers next to each row. pos thx lazy i will try and make something up from your answer. Unless anyone else got a quick answer.

Hi, any one know how to display a simple numeric value next to the results of a select query? why cant i find the correct search term to do this?? i want the result to be numbered with a simple 1-10 position.=column = pos pos name id leads 1 Williams 1 12 2 bond 9 4 3 brown 5 3 4 williams 3 3 5 you 6 2 6 eyes 7 2 etc i can only find row_num which gives me the record number of the row...

did my attachement not show up? :::: so the 10, 3, 4, 0 show up but not in the 'total year ' column Company id id Name Total Ever Total Year 1 1 Richio Williams 11 1 3 lisa williams 3 1 9 billy bob 4 1 16 billy jo 0 10 3 4 0 Total 18