Jump to content

kickstart

Staff Alumni
 View Profile  See their activity

  • Posts

    2,707

  • Joined

  • Last visited

 Content Type 

Posts posted by kickstart

    Age grouping by Date of birth

    in MySQL Help

    Posted

    Hi

     

    Give this a try:-

     

    SELECT c.*, 
a.ageGroup, 
DATE_FORMAT(FROM_DAYS(TO_DAYS(CONCAT(YEAR(NOW()), '-08-31'))-TO_DAYS(c.Dob)), '%Y')+0 AS age
FROM child c
INNER JOIN ageGroup a  ON  DATE_FORMAT(FROM_DAYS(TO_DAYS(CONCAT(YEAR(NOW()), '-08-31'))-TO_DAYS(c.Dob)), '%Y')+0 BETWEEN a.min_age AND a.max_age
WHERE a.agegroup_ID = team
GROUP BY c.child_ID
ORDER BY c.lastName ASC

     

    All the best

     

    Keith

    Php mysql select highest quotient

    in MySQL Help

    Posted

    Hi

     

    Select the calculated value and order by that descending. Then use a limit clause on the select?

     

    Not 100% sure that will give you what you want (ie, if you wanted the top 10, if will give you the top ten rows, rather than the rows with the top ten values if those values are duplicated).

     

    All the best

     

    Keith

    fetch teacher and subject wise details in mysql and php

    in MySQL Help

    Posted

    Hi

     

    Not 100% sure I know what you are trying to find.

     

    I think you are trying to get all the teachers for a specified institute where the classes they take don't have 25 students

     

    If so:-

     

    SELECT institute, teacher, subject, count(students) AS StudentCount
FROM tuition
WHERE institute = 'someinstitute'
GROUP BY institute, teacher, subject
HAVING StudentCount < 25

     

    All the best

     

    Keith

    img help

    in MySQL Help

    Posted

    Hi

     

    I would guess that something is being output before the image. Possibly an error message, possibly a line feed from a blank line before your first <?php tag.

     

    All the best

     

    Keith

    Would I use an OUTER JOIN?

    in MySQL Help

    Posted

    Hi

     

    I presume there are multiple courses per person, in which case I think the above suggestion will bring back all the other courses people have done

     

    I would try:-

     

    SELECT table_1.* 
FROM table_1 
LEFT OUTER JOIN table_2 
ON table_1.emp_number = table_2.emp_number AND table_2.course_title = 'title'
WHERE location = 'locaction'
AND course_title IS NULL;

     

    This is using a LEFT OUTER JOIN against the courses table, but checking the course title in the ON clause. This way anyone who has done that course will have a row returned from the join with the course title populated while anyone else will have a row returned from the join with the course title being null. The where clause then drops the rows where the course title is not null.

     

    All the best

     

    Keith

    Select teams' last N games

    in MySQL Help

    Posted

    Hi

     

    I can think of a way to do it (although not sure how efficient).

     

    Doing something similar on a table of mine the following works:-

     

    SELECT *
FROM
(SELECT @rownum:=@rownum+1 rownum, testresults.id, testresults.Client, testresults.MonthYear
FROM (SELECT @rownum:=0) r, testresults
ORDER BY Client, MonthYear DESC) x
INNER JOIN (
SELECT Client, MIN(rownum) AS rownum
FROM
(SELECT @rownum:=@rownum+1 rownum, testresults.id, testresults.Client, testresults.MonthYear
FROM (SELECT @rownum:=0) r, testresults
ORDER BY Client, MonthYear DESC) x
GROUP BY Client) z
ON x.Client = z.Client
WHERE x.rownum < z.rownum + 10

     

    This is using a subselect to get all the records with a row number on them in descending date order. The subselect is also used to get the the smallest row number for each client. The 2 are joined together and only records where the row number is less that the min row number plus 10 (ie, the latest 10) is retrieved.

     

    There might well be a more efficient way to do this.

     

    If you can supply a layout of your table and some example data I will try and modify it to work for what you want.

     

    All the best

     

    Keith

    confused on list results

    in MySQL Help

    Posted

    Hi

     

    On that page it is probably easiest to loop through building up a long insert string.

     

    Note that in your code you have 2 fields called Name which will cause problems.

     

    I would specifically set the array numbers for the fields:-

     

    $fieldCnt = 0;
while ($row = mysql_fetch_assoc($result)) 
{
    //we will echo these into the proper fields

    echo '<tr>
                <td align="center"><input name="Name[$fieldCnt]" type="text" id="Name" value="'.$row['Code_P'].'"></td>
                <td align="center"><input name="Contact[$fieldCnt]" type="text" id="Contact" value="'.$row["Nom"].'" size="15"></td>
                <td>
                    <select name="Periode[$fieldCnt]">
                        <option value="1">1</option>
                        <option value="2">2</option>
                        <option value="3">3</option>
                        <option value="4">4</option>
                    </select>
                </td>
                <td>
                 <input name="NameSelect[$fieldCnt]" type="checkbox" id="Select" value="'.$row['Code_P'].'">   
                </td>
            </tr>';
$fieldCnt++;
}

     

    Then I would add a hidden id field for each name.

     

    When the page is returned use a foreach on one of the fields, check the values returned and if required add them to the required insert.

     

    Things get a touch more difficult when you want to remove items from the database when they have been unselected.

     

    All the best

     

    Keith

    confused on list results

    in MySQL Help

    Posted

    Hi

     

    It is difficult to know without seeing how other things are already laid out.

     

    However I would assume you have a table of students and another table containing classes.

     

    You would then have a 3rd table linking the 2 together. Maybe called StudentClass with the following fields.

     

    Id

    StudentId

    ClassId

     

    So if student with StudentId 5 had 3 classes with ClassId of 1,4 and 6 you could do an insert like:-

     

    INSERT INTO StudentClass (Id, StudentId, ClassId) VALUES (NULL, 5, 1), (NULL, 5, 4), (NULL, 5, 6)

     

    All the best

     

    Keith

    join question

    in MySQL Help

    Posted

    Hi

     

    Can you do an actual export of the data.

     

    The code I posted did work when I set up a copy of what I believe the be the table layouts using the data you supplied in the arrays.

     

    You code won't work properly at the moment as each sub category has its own unordered list.

     

    All the best

     

    Keith

    confused on list results

    in MySQL Help

    Posted

    Hi

     

    Problem is your table design.

     

    You are best off having a table linking students to classes, with a separate line per student and class. So if the student has 3 classes there will be 3 rows.

     

    If you want to keep your current design (which will cause problems later on when trying to do more complex SQL) then assuming Etudiant is a string field change

     

    $id = "('" . implode( "','", $checkbox ) . "');" ;

     

    to

     

    $id = "('" . implode( ",", $checkbox ) . "');" ;

     

    All the best

     

    Keith

    join question

    in MySQL Help

    Posted

    Hi

     

    Assuming the arrays data should have been in mysql tables then this does it for you.

     

    <?php

$sql = "SELECT * 
FROM categorys
INNER JOIN sub_categorys ON categorys.cat_id = sub_categorys.cat_id
ORDER BY categorys.cat_id, sub_categorys.sub_cat_id";

$query = mysql_query($sql) or die(mysql_error()." $sql");

$Category = '';

echo "<ul>";
while($row = mysql_fetch_array($query))
{
if ($row['category'] != $Category)
{
	if ($Category != '')
	{
		echo "</ul>";
	}
	$Category = $row['category'];
	echo "<li>" . $row['category'] . "</li>";
	echo "<ul>";
}
echo "<li>".$row['sub_category']."</li>";
}
if ($Category != '')
{
echo "</ul>";
}
echo "</ul>";

?>

     

    All the best

     

    Keith

    join question

    in MySQL Help

    Posted

    Hi

     

    Can you export your 2 tables with data and post them here. Then I can have a play to get it to work.

     

    As it is I can only guess as to which items you mention are categories, which are sub categories and which relate to which.

     

    All the best

     

    Keith

    join question

    in MySQL Help

    Posted

    Hi

     

    You want to create a new <ul> set of tags when the category changes, and close the previous set if they existed.

     

    Something like this (not tested so excuse typos).

     

    <?php 
$Category = '';
$query = mysql_query("SELECT * FROM categorys INNER JOIN sub_categorys ON categorys.cat_id =  sub_categorys.cat_id");

echo "<ul>";
while($row = mysql_fetch_array($query))
{
if ($row['category'] != $Category)
{
	if ($Category != '')
	{
		echo "</ul>";
	}
	$Category = $row['category'];
	echo "<li>" . $row['category'] . "</li>";
	echo "<ul>";
}
echo "<li>".$row['sub_category']."</li>";
}
if ($Category != '')
{
echo "</ul>";
}
echo "</ul>";
?>

     

    All the best

     

    Keith

    Group by query unless column=1

    in MySQL Help

    Posted

    Hi

     

    It is, and not sure there is an elegant way to do it.

     

    Best way I can think would be 2 queries UNIONed together, one for the admin and one for the non admin rows.

     

    Something like (please excuse any typos):-

     

    SELECT *, SUM(quantity) as item_quantity
FROM orders_items
WHERE orders_id="1" 
AND admin = 0
GROUP BY  code, sizes_id, sizes_id_2, colours_id
UNION ALL
SELECT *, quantity as item_quantity
FROM orders_items
WHERE orders_id="1" 
AND admin = 1

     

    All the best

     

    Keith

     

    Group by query unless column=1

    in MySQL Help

    Posted

    Hi

     

    Bit uncertain what you want to count.

     

    You could just add the admin column to the group by. This would give you a row for admin = 0 and a row for admin = 1 for each set of data which contained both.

     

    You could do something like this:-

     

    SELECT *, SUM(IF(admin = 1, 0, quantity)) as item_quantity
FROM orders_items
WHERE orders_id="1" 
GROUP BY  code, sizes_id, sizes_id_2, colours_id
ORDER BY id ASC

     

    All the best

     

    Keith

    Setup Array to define form elements to generate

    in PHP Coding Help

    Posted

    Hi

     

    How simple will the form designs be?

     

    I have written a small system that takes an XML file that defines the input elements and then puts out a series of pages each with those elements on it (final output to save the data was to another XML file).

     

    With a limited number of questions per page this is fairly easy to do (doing a VB version of it was far more difficult). The structure including the entered data was just stored in an Object that was stored as a session variable, and once data input was complete it was processed.

     

    However this does mean having small and fairly generic input forms.

     

    All the best

     

    Keith

    Can you "eval" a word?

    in PHP Coding Help

    Posted

    Hi

     

    Just realised the code I pasted earlier was garbage and might have confused you.

     

    Something more like this:-

     

    <?php
$j=1;
if ($row = mysql_fetch_array($rs))
{
for($j = 1;$j<=10;$j++)
{
	$arrayvar[$j] = $row['field'.$j];
}
}
?>

     

    All the best

     

    Keith

    Can you "eval" a word?

    in PHP Coding Help

    Posted

    Hi

     

    I agree with PFMaBiSmAd.

     

    If they are columns then nothing to stop you doing something like as follows:-

     

    <?php
$j=1;
while ($row = mysql_fetch_array($rs))
{
$arrayvar[$j] = $row['field'.$j];
$j++;
}
?>

     

    All the best

     

    Keith

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.