bululu

@Ch0cu3r Thanks very much for your kind contribution. I am going to be trying the join, and if I fail will look at the two (2) query version as I am not yet comfortable with joins.

Hi PHP Experts, I need some help. I have two tables as follows names table id name man_no 1 Alex 12340 2 Anne 12341 3 Ben 12342 4 Jude 12343 5 Carlos 12344 6 Goofy 12345 scores table id score man_no 1 12 12340 2 5 12341 3 0 12342 1st query $query = "SELECT * FROM names'"; $result = mysqli_query($dbc, $query); ($row = mysqli_fetch_array($result)); $name = $row["name"]; $mat_no = $row["man_no"]; 2nd query $query = "SELECT * FROM scores WHERE man_no='$man_no'"; $result = mysqli_query($dbc, $query); ($row = mysqli_fetch_array($result)); $man_no = $row["man_no"]; Then var_dump($score); gives me string '12' (length=2) string '5' (length=1) string '0' (length=1) null null null Now, here is my question: The Names table will always have people who do not have scores as the scores are added when available. The first query gets the man numbers and using the man numbers, queries for each man number's associated score, returning nill where a score is unavailable. What I want is to get a result set that only incldes scores available in mysql without the null values, a way to avoid null values in the result set. I am still learning joints so I do ask that your help deals with individual queries as I am not yet at a I can use joins. Is it possible to restrict mysql results to only when an entry is available in MySQL. In the above example, is there a clause I can add to the query to only return results where a value was found? Or may be killing the null values after the query? I could not find a way to kill them! My aim is to have: string '12' (length=2) string '5' (length=1) string '0' (length=1)

@Barand, I removed the quotes as you advised, I used : while ($data = fgetcsv($handle,100000)) and it works flawlessly regardless of any comas or quotes! Would you, please care to explain because I really thought those quotes were needed in there so am wondering why they were even there in the first place! Thanks so much, you saved me from running crazy!

I am having trouble importing a CSV file into MyQL using PHP's fgetcsv, because of comas in one or more fields. I have successfully uploaded files where all fiels do not have comas. As expected, the presence of comas messes up everything as the item with comas is inserted in multiple columns instead of its allocated column. How do I escape any number of comas? I have tapped my head several times and I cannot come up with a way to do it. Please help! Viewed in a Text Editor, my CSV looks like: 2013-10-29,301,Mr Peter Jones,Father,"Leeds Industries Ltd, 17 Ice Se, LS7 3BD, Leeds",0796000056,01132345678,10,A 2013-10-29,302,Ms Adel Martins,Mother,"Cosmetics Industries Bradford, ",0746000087,01242545671,10,A 2013-10-29,303,Mrs Joyce Malaiti,Auntie,"The Hedrow, No 213, Leeds, LS9 3TT",0756000034,01133451321,10,A 2013-10-29,304,Mr Phiri,Father,"PO Box 123456, Leeds",0776000090,,10,A 2013-10-29,305,Mr Bismark,Uncle,"Plot 56789 Albion Street, Leeds, LS4 3EE",0776000032,01137123465,10,A As you can see, I am including the date because I could not use the MySQL NOW() function to handle the date. Is there a way to automate datae insertion when importing a csv file? The code is as follows: ?php //connect to the database $connect = mysql_connect("localhost","root","password"); mysql_select_db("some_db",$connect); //select the table // if ($_FILES['csv']['size'] > 0) { //get the csv file $file = $_FILES['csv']['tmp_name']; $handle = fopen($file,"r"); //loop through the csv file and insert into database do { if ($data[0]) { mysql_query("INSERT INTO youths (date,id,guardian,relationship,address,mobile,landline,grade,class) VALUES ( '".addslashes($data[0])."', '".addslashes($data[1])."', '".addslashes($data[2])."', '".addslashes($data[3])."', '".addslashes($data[4])."', '".addslashes($data[5])."', '".addslashes($data[6])."', '".addslashes($data[7])."', '".addslashes($data[8])."' ) "); } } while ($data = fgetcsv($handle,100000,",","'")); // //redirect header('Location:guardian_csv_mysql.php?success=1'); die; } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Import a CSV File with PHP & MySQL</title> </head> <body> <?php if (!empty($_GET['success'])) { echo "<b>Your file has been imported.</b><br><br>"; } //generic success notice ?> <form action="guardian_csv_mysql.php" method="post" enctype="multipart/form-data" name="form1" id="form1"> Choose your file: <br /> <input name="csv" type="file" id="csv" /> <input type="submit" name="Submit" value="Submit" /> </form> </body> </html>

Everything is fine, the root user has ALL previleges! I can't understand why it is not working!

@Barand, you readit before I edited. I used it as above key instead of man_number, but the same problem.

If I have the following Array : $people=array(1234 => 1, 1235 => 4, 1237 => 3, 1239 => 5); Is it possible using PHP, to insert an array like above, temporarily such that the table is automatically destroyed at the end of the PHP session? The idea is to be able to select from the table later down the script, since that table should last as long as the session. I have read about Temporary MySQL table and looked at examples, but at my stage, I can not wrap my brain around it. Is the above wish possible in PHP/ MySQL?

Thanks guys - Barand, Vinny42 and Kicken, for sharing your expertise, I am learning alot and I really appreciate. This has been solved.

This looks great. I do not have the rank column, only the name and score. Users provide a name and a score and when that data is queried, the ranks are to be calculated. Do I need to add a rank column to the database? How does it work since I just supply the data for name and score?

If a tie occurs at the end, the preceding position is skipped, so if there are 3 students at number 9, we skip positions 7 and 8, so we should have. AS can be seen, 9 students sat for the test and the last scorers are at number 9. Positions are maintained though some have been forfeited e.g number 2, because two decided to be at number 1. 70 1 70 1 69 3 57 4 46 5 45 6 40 9 40 9 40 9 Yes, it comes from a MySQL Database. If the ranks/ positions were straightforward i.e just 1,2,3, etc then I would use mysql functions. I cannot see how to do it from MyQL as the data is being retrieved. Can MySQL functions handle this degree of ranking?

I have the following array: $learners=array('Eliza'=87, 'Joe'=81, 'Anne'=69, 'Marley'=39, 'Teddy'=39, 'Jemma'=90, 'Sylvia'=87); So far I have been able to separate the two arrays as follows: $tudents=array_keys($learners); $scores=array_values($learners); The ranking is as follows: Student Score Position Jemma 90 1 Sylvia 87 2 Eliza 87 2 Joe 81 4 Anne 69 5 Marley 39 7 Teddy 69 7 I would like to create a new array with names as keys and positions as values i.e $positions=array('Jemma'=1, 'Sylvia'=2, 'Eliza'=2, 'Joe'=4, 'Anne'=5, 'Marley'=7, 'Teddy'=7); This will allow me to echo any name and position at any point on the script. The ranking is not straightforward if the scores have duplicates. If there is a tie at number 2, the 3rd position is skipped. If the tie occurs at the end of the scores, then both scores will be placed at the last position and the preceding position will be skipped, in the example above, position 6 has been skipped and the two 39s occupy position 7. Any help will be appreciated

@Ch0cu3r, you are the Genius! have tested it and It works like you coded it to and like I wanted it to! Thanks very much!

Hi PHP Frieks, I have a problem that I wish you guys will solve (thanks in advance). I am pulling data from mysql (the Payment Column) and want to do running totals using PHP in column called Runnng Totals. As can be seen we have I prefer a PHP solution to the running totals as opposed to doing te totals in a MyQL query. ID Payment Running Totals 1 250 250 2 300 550 5 220 770 7 100 870 I have not yet come up with any code worthy posting as I am still fiddling with some code, and will post my efforts as soon I can come up with something sensible. Any help will be deeply appreciated.