Hi! I’m Starx

Question 1

Question by Andrew Latham

I am trying to select the classes with maximum enrollment in each department from a table with the following structure:

Courses: cid, dept, name

Enrollment: cid, sid

The idea would be to count all the enrollments in each class, and find the maximum in each department. Unfortunately, I’m having trouble with the maximum-count combination.

My query was going to look something like:

 SELECT c.name, MAX(COUNT(*) FROM Enrollment E WHERE E.cid = C.cid)
 FROM Courses C
 GROUP BY C.dept

But I can’t figure out how to get the MAX-COUNT syntax to work properly. I’ve tried a lot of different examples from Google and StackOverflow and none of them worked for me. MySQL says the syntax is wrong.

Answer

Answer by dbaseman

dbaseman

I like nested queries for this kind of problem. First select the enrollment counts grouped per class. Then find the max enrollment count grouped per department:

SELECT MAX(cnt) cnt, dept FROM
(
    SELECT COUNT(*) cnt, dept, C.cid
    FROM Courses C 
        INNER JOIN Enrollment E on C.cid = E.cid
    GROUP BY cid, dept
) a
GROUP BY dept

Answer

Answer by Starx

Starx

There is no declaration of E, so you can’t use E.cid

So, either you do this

SELECT c.name, COUNT(c.*) as count 
FROM Courses C
GROUP BY C.dept

Or,

 SELECT c.name, MAX(SELECT COUNT(*) FROM Enrollment E WHERE E.cid = C.cid)
 FROM Courses C
 GROUP BY C.dept

Question 2

Question by max_

max_

I am testing out IF statements in MySQL for the first time and I am coming across an error.

I am trying to firstly, find out how many rows are in the table, then depending on whether the count is less than or equal to five, return all of the rows in the table, or if it isn’t, return all posts in the range used within the LIMIT clause.

The error I am receiving is as follows;

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ‘IF SELECT COUNT(*) FROM Posts <= 5 THEN SELECT * FROM Posts
ELSE SELECT * FR’ at line 1

Please can you tell me where I am going wrong?

Max.

$query = mysql_query(
    "IF SELECT COUNT(*) FROM `Posts` <= 5 
     THEN 
         SELECT * FROM `Posts` 
     ELSE 
         SELECT * FROM `Posts` LIMIT $from, $limit") 
     or die(mysql_error());

Answer

Answer by Starx

Starx

you don’t need to do this. Mysql handles this automatically, just use

$query = "SELECT * FROM `Posts` LIMIT $from, $limit";

Question 3

Question by Anandhan

Anandhan

I’m new to jquery & javascript.. I need to create a drop-down with rounded corner that will work based on hover as shown below..

enter image description here

Answer

Answer by Starx

Starx

Give border-radius on the hover event

Using CSS

select:hover {
    border-radius: 10px;
    -webkit-border-radius: 10px; /* For safari */
    -moz-border-radius: 10px; /* For mozilla */
}

Here is an example using jQuery Snippet

$("select").hover(function() {
    $(this).css("borderRadius",10px);
});

Question 4

Question by Vehlad

Vehlad

Multiple categories selection system for Article

Earlier M using two tables
articles & categories and save category ID in articles table
But in this system I can save only one category ID per article
I want to save Article in Multiple categories

While searching I found same question on StackOverflow

I understand the whole concept of adding one more table of relationship & saving Article ID & Category ID in this table.
But not aware how to implement multiple selection system using arrays in New Article Form & Edit Article Form.

earlier I am showing Select in my form to display categories list in Add Article & Edit Article Page.

pls someone explain me How to show categories list in multiple checkbox style in my form so user can select multiple categories and then after POST how to get checkbox data and run query to insert data in both Article Table & New Relationship table with selected categories ID

want to display category List like this screenshot

Many Many Thanks…

EDIT:
I use echo '<input type="checkbox" name="selcats[]" value="$catid"> ' .$catname;
to display Categories check box
Its showing in a row side by side.
how to change display like screenshot i.e. list with scrollbar
& need to process this array and insert in new table while inserting article in databse.

EDIT 2
got the checkbox display correct in a scroll list by using a div 😀
<div style="height: 150px; width: 200px; overflow: auto;">

Answer

Answer by Foreba

Foreba

In my case, I use a “tag system”, for instance: You have an article with one category, nothing will change that… In adition, you can create another field in the article table with the relevant words (or whatever you want) and separete them with spaces or commas.

// Get the data from your database
$tags = 'world, travel, turism, cities';

// Separate the values
$tags = explode(", ", $tags);

// Create a link for each one
foreach($tags as $t)
{
    echo ' <a href="?tag=' . $t . '">' . ucfirst($t) . '</a> ' . "rn"; 
}

It should output:

 <a href="?tag=world">World</a> 
 <a href="?tag=travel">Travel</a> 
 <a href="?tag=turism">Turism</a> 
 <a href="?tag=cities">Cities</a>

And it means that you can SELECT articles that have the title LIKE the tag, or whatever you want to search.

Is that what you was looking for?

Answer

Answer by Starx

Starx

You basically require two tables, with this structure

table_categories

_____________________________
|    id    |      title    |
-----------+---------------+

table_category_detail

______________________________________________
|    id    |  categoryId   |    articleId    |
-----------+---------------+------------------

To extract all categories, select all from the table_categories and put up into the select menu with mutiple selection enabled.

Next, when posted get the selected box values and insert into table_category_detail one by one

This is how you create a select box

$query = "SELECT * FROM table_categories";
$result = mysql_query($query);
echo '<select multiple="multiple">';
while($row = mysql_fetch_assoc($result)) {
   echo '<option value="'.$row['id'].'">'.$row['title'].'</option>';
}
echo "</select>";

Or a Multiple Check Box

while($row = mysql_fetch_assoc($result)) {
   echo '<input type="checkbox" name="selcats[]" value="'.$row['id'].'"> ' .$row['title'];
}

After the post:

 // Here write codes to insert the article first

 $articleId = mysql_insert_id(); //get the id
 $values = $_POST['selcats'];
 foreach($values as $value) {
      $query = "INSERT into `table_category_detail` VALUES(NULL, $value, $articleId)";
      $result = mysql_query($result);
 }

Question 5

Question by Amila

Amila

I have the following table in my page

<table>
<tr class="evenPack">
</tr>
<tr class="evenSubPack">
</tr>
<tr class="evenSubPack">
</tr>
<tr class="evenSubPack">
</tr>
<tr class="evenSubPack">
</tr>
<tr class="oddPack">
</tr>
<tr class="oddSubPack">
</tr>
<tr class="oddSubPack">
</tr>
<tr class="oddSubPack">
</tr>
<tr class="oddSubPack">
</tr>
<tr class="evenPack">
</tr>
<tr class="evenSubPack">
</tr>
<tr class="evenSubPack">
</tr>
<tr class="evenSubPack">
</tr>
<tr class="evenSubPack">
</tr>
</table>

The evenPack rows and oddPack rows are clickable. When the user clicks on one of them, I want the immediate SubPack rows to toggleSlide().

for example, if the user clicks on the first row, which is a evenPack. Then only the four evenSubPack rows that are immediately after the first evenPack should toggleSlide. It shouldn’t toggleSlide() the evenSubPack rows that are at the bottom of the table.

I can’t think of the selector to do this.

Answer

Answer by Starx

Starx

You can use nextUntil() for this:

$("tr").click(function() {
    var active =  $(this).attr("class").indexOf('even') > -1 ? 'odd' : 'even';
    //find if the clike in even, and find out out the next stop point will be
    $(this).nextUntil('.' + active + 'Pack').slideToggle();
});

Demo

Question 6

Question by Jordy

Jordy

How can I select all records where the id is in the array?

So if I have an array with the values 1,25,2,12,1859 and 192: all the records with the id’s should be returned.

Now I have this:

$query="SELECT * FROM table WHERE Id IN(".implode(",",$my_array).")";

But that doesn’t work. It only returns one value.

Answer

Answer by Starx

Starx

Add the quotes in the query

$query="SELECT * FROM table WHERE Id IN('".implode("','",$my_array)."')";

Question 7

Question by php_d

php_d

UPDATE I have updated my code in response to @MichaelRushton comments. I am now using Highcharts but I am having trouble getting output to the data series.

I now have the following array generated from a mysql query, and I would like to output it into a line chart. My Y-Axis should contain the amount, x-axis is the date range, and legend is the different items plotted on the chart.

   // Call the stored procedure
   $stmt->execute();                    

   while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
       $array[$row['legend']][$row['date']] = $row['amount'];
       //print_r($array);
   }


   chart = new Highcharts.Chart({

   chart: {
     renderTo: 'container',
     type: 'line'
   },

   xAxis:
   {
    categories: [2012-03-01, 2012-03-02, 2012-03-03, 2012-03-04, 2012-03-05, 2012-03-06, 2012-03-07, 2012-03-08, 2012-03-09, 2012-03-10, 2012-03-11, 2012-03-12, 2012-03-13, 2012-03-14, 2012-03-15, 2012-03-16, 2012-03-17, 2012-03-18, 2012-03-19, 2012-03-20, 2012-03-21, 2012-03-22, 2012-03-23, 2012-03-24, 2012-03-25, 2012-03-26, 2012-03-27, 2012-03-28, 2012-03-29, 2012-03-30, 2012-03-31],
   },

   series:
   [

<?php
     foreach ($array as $legend => $data)
         {
           echo '{';
           echo "name: '" . $legend . "',";

           $values = array();

           for ($i = 1; $i <= 31; ++$i)
           {
             $values[] = isset($data[$i]) ? $data[$i] : 0;
           }

           echo 'data: [' . implode(', ', $values) . '],';
           echo '},';

         }
?>
],
 }
 );

<div id="container" style="width: 100%; height: 400px"></div>
)

This code is presenting me with the following output:

series: [ {name: 'Something Tastier',data: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0],},{name: 'Something Tasty',data: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0],}, ], } );

The seems right except there is no values outputting to the data series. If anyone has any further ideas it would be much appreciated.

Answer

Answer by MichaelRushton

MichaelRushton

Highcharts is my favourite option for graphs. It might be worth structuring your array to look like:

Array (

  [legend_one] => Array
  (

    [2012-03-01] => 100
    [2012-03-02] => 200
    [2012-03-03] => 300
    ...

  )

  [legend_two] => Array
  (

    // Day of the month    
    [2012-03-01] => 100
    [2012-03-02] => 200
    [2012-03-03] => 300
    ...

  )

  ...

)

You can then use Highcharts like this:

Edit: Now uses full date rather than just day, made the month dynamic (using $start variable), and started the day iterator at 0 rather than 1 to remove the need for $i - 1 when using strtotime to work out the next date.

chart = new Highcharts.Chart({

  xAxis:
  {

    categories: [

<?php

      // You could dynamically set this date using $_GET/$_POST
      $start = '2012-03-01';

      $dates = array();

      for ($i = 0, $days = date('t', strtotime($start)); $i < $days; ++$i)
      {
        $dates[] = date('Y-m-d', strtotime($start . ' + ' . $i . ' day'));
      }

      echo "'" . implode("', '", $dates) . "'";

?>

    ],

  },

  series:
  [

<?php

    foreach ($array as $legend => $data)
    {

      echo '{';

      echo "name: '" . $legend . "',";

      $values = array();

      for ($i = 0; $i < $days; ++$i)
      {

        $date = date('Y-m-d', strtotime($start . ' + ' . $i . ' day'));

        $values[] = isset($data[$date]) ? $data[$date] : 0;

      }

      echo 'data: [' . implode(', ', $values) . '],';

      echo '},';

    }

?>

  ],

}
);

Answer

Answer by Starx

Starx

You are going the wrong way. If you searching for something that is already available, then you should output the data in the way, they accept. Not the other way around.

With that being said, try pChart. It has wide support for different formats.

Question 8

Question by user1286499

user1286499

may I know how do I calculate time left from specific datetime in php?

Example I want user can request a new confirmation code after 30 minute if already requested.

$request_date = '2012-03-24 13:03:17';
$please_wait  = '30 minutes';

How do I calculate the time left?

Answer

Answer by safarov

safarov

Time left for new confirmation code:

$request_date = '2012-03-24 13:03:17';
$please_wait  = '30 minutes';
$future_time = strtotime("+" . $please_wait, strtotime($request_date));
$time_left = $future_time - time(); //seconds left

Answer

Answer by Starx

Starx

Calculate the requested timestamp and sustract with currect time stamp

Solution:

$future = strtotime($request_date)+1800; // 1800 is 30 minutes
echo date("i", $future-time()); //this is the remaining minutes

Question 9

Question by Ryan Detzel

Ryan Detzel

It doesn’t always happen so it makes me think it happens when the browser doesn’t have the css cached. What happens is the page loads and you see the entire page without any css and then it “pops” in styled. The css files are hosted off a different domain and they’re all in the header of the document. Any thoughts?

Browser around the source I noticed that we have google optimizer code on some pages. Could this cause this to happen?

Answer

Answer by Starx

Starx

There might be many reasons, behind this, but as you described:

Your CSS are not included in inside the <head>

Question 10

Question by Sedat KURT

Sedat KURT

I have abc.php and def.php files.For example I want to call def.php from abc.php .I found include() method but I don’t want def.php to activate in background.I want browser to redraw the page according to html-php codes of def.php

Any ideas ?

Answer

Answer by Starx

Starx

You can return the content of def.php

Here is an example:

Page: def.php

<?PHP
  //do many things, but do not echo or print anything
  $output ="<div> all the codes</div>"; // collect the markup like this
  return $output; // return the output but still do not echo or print
?>

Now Page: abc.php

//do its own processing
$myDefPageOutput = include("def.php"); 
//This will only get the values of the return statement

//Now user $myDefPageOutput wherever you want and redraw the page

Hi! I’m Starx

experienced Software Developer. And this is my blog

Blog Page

MySQL count maximum number of rows

Question by Andrew Latham

Answer by dbaseman

Answer by Starx

IF Statement in MySQL, using THEN and ELSE

Question by max_

Answer by Starx

Rounded Corner single Dropdown menu with Hover functionality

Question by Anandhan

Answer by Starx

One Article multiple categories system in PHP SQL

Question by Vehlad

Answer by Foreba

Answer by Starx

jQuery Selector to Select Sections of a Table

Question by Amila

Answer by Starx

Demo

Select where in array

Question by Jordy

Answer by Starx

Generate a highchart based on the output of an array

Question by php_d

Answer by MichaelRushton

Answer by Starx

Calculate time left from specific datetime in php

Question by user1286499

Answer by safarov

Answer by Starx

HTML un-styled is shown first and then changes to styled version, what could cause this?

Question by Ryan Detzel

Answer by Starx

Activating a php file from another php file

Question by Sedat KURT

Answer by Starx

Please fill the form - I will response as fast as I can!