May 27, 2013

How to provide table name to select from a search text box like: SELECT * FROM $query

User2425381’s Question:

I cant find the answer anywhere, maybe someone could help me. I need to display my MySQL table, but I need to give the name of the table through search box.
Something like this:

$raw_results = mysql_query("SELECT * FROM $query") or die(mysql_error());

Your code already does that, only thing remaining is to pass the value to the variable $query

$query = "your search query";

$raw_results = mysql_query("SELECT * FROM $query") or die(mysql_error());

However, what you are doing is very vulnerable, you should not pass the table name from the text box to the code directly.

$query = $_POST['searchbox'];

Doing such will leave your code very vulnerable as I could type in users and get all the details of the user.

Change your programming, whatever you are trying to do is wrong.

May 16, 2013

Need to know if mysql query can get this type of result

Sravis’s Question:

I have a Table structure like below

+--------+---------+
| cat_id | user_id |
+--------+---------+
|     10 |       1 |
|     10 |       2 |
|     11 |       3 |
|     11 |       4 |
+--------+---------+

I’m trying to get the result like below

Array
(
    [cat_id] => 10,
    Array
    (
        [user_id] => 1,
        [user_id] => 2
    )
)

Array
(
    [cat_id] => 11,
    Array
    (
        [user_id] => 3,
        [user_id] => 4
    )
)

I tried using group it didn’t work, If i try with sub query i get error message “Subquery returns more than 1 row.”

Is it possible to achieve this kind of result using mysql query?

No, MySQL api funcions provide a result set of your records. They cannot customize to your requirements. You have to create your own functions to develop that format.

Start by getting unique category id and they go through them in a loop to get there remaining details.

$mainArray = []; //Create a array to store the records
$result = mysqli_query($conn, "SELECT DISTINCT('cat_id') as catid FROM `yourtable`");
while($row = mysqli_fetch_assoc($result)) {       

    //Now another query to fetch the users
    $res = mysqli_query($conn, "SELECT * FROM `yourtable` WHERE cat_id='$row[0]'");

    $users = array(); //array to store users
    while($r = mysqli_fetch_assoc($res)) {
         $users[] = $r['user_id'];
    }

    // Now add to your main array
    $mainArray[] = array('cat_id' => $row['catid'], "users" => $users);
}
var_dump($mainArray);
March 3, 2013

mySQL previous/next query

Question by StealthRT

I am trying to figure out a way to have a previous and next button to display more data for a given query-sorta like a pagination would do.

The query is this:

$query = "SELECT * 
        FROM wp_posts p
            LEFT OUTER JOIN wp_term_relationships r ON r.object_id = p.ID
            LEFT OUTER JOIN wp_term_taxonomy x ON x.term_taxonomy_id = r.term_taxonomy_id
            LEFT OUTER JOIN wp_terms t ON t.term_id = x.term_id
            LEFT JOIN wp_postmeta pm ON p.id = pm.post_id
        WHERE p.post_status = 'publish'
        AND pm.meta_key = 'xTraData'
        AND p.post_type = 'post'
        AND t.slug = 'press-coverage'
        ORDER BY p.post_date DESC LIMIT 0,6;";

How can i get the previous id and next id to use for the next/previous buttons for refreshing the query string?

Answer by Starx

You could get them using $_GET variables, which can be sent from URL. Process them to raise you limits.

A mysql limit defines how much rows to fetch and from where to fetch.

So, 0, 6 in your query say start from 0 (first) and select 6 rows.

$p_num = isset($_GET['pagenumber']) && (int)$_GET['pagenumber'] > 0 ? $_GET['pagenumber'] : 0;
$p_size = 6;

$query = "SELECT * 
        FROM wp_posts p
            LEFT OUTER JOIN wp_term_relationships r ON r.object_id = p.ID
            LEFT OUTER JOIN wp_term_taxonomy x ON x.term_taxonomy_id = r.term_taxonomy_id
            LEFT OUTER JOIN wp_terms t ON t.term_id = x.term_id
            LEFT JOIN wp_postmeta pm ON p.id = pm.post_id
        WHERE p.post_status = 'publish'
        AND pm.meta_key = 'xTraData'
        AND p.post_type = 'post'
        AND t.slug = 'press-coverage'
        ORDER BY p.post_date DESC LIMIT ".(($p_num-1) * $p_size).",".$p_size.";";

Now, send a request like: yourpage.php?pagenumber=2 and it will query the second page where a page will hold about 6 items.

October 28, 2012

Suggestions with pagination

Question by dorin dorin

I’ve a problem with pagination. The php code of pagination query is:

$ <? if (isset($_GET["page"])) { 

    $Page = preg_replace("/[^0-9]/","", $_GET["page"]);
} else {
    $Page = 0;
}
$limit       = 10;
$StartFrom  = $limit * $Page;
$TotalFiles = mysql_num_rows(mysql_query("SELECT * FROM linkuri WHERE `categorie` = 'Afaceri' AND status = 1"));
$TotalPages = floor($TotalFiles / $limit); ?>

And code to display it:

$i = 0; while ($TotalPages >= $i) { echo '<a class="active imgf" style="opacity: 1;margin-bottom:3px; margin-top:3px;" href="afaceri.php?page='.$i.'">'.($i+1).'</a>';$i++;}

The problem is I am trying to make the display as: PAGES: "BACK" 1 2 3 4 5 "NEXT"

Answer by Starx

First I would like to suggest an improvement on your query. To count the total rows use:

SELECT count(*) as `totalpost` FROM linkuri WHERE `categorie` = 'Afaceri' AND status = 1

Then, you are create that pagination by doing something like this:

$totalPost = 50; //Dummy total post
$limit = 10;

$pages = $totalPost / $limit; //Giving 5

//Now we know there are five pages
for($i=1; $i<=$pages; $i++) {
  echo $i; // Better echo something like <a href="link">$i</a>
}

P.S: This is a very basic example

After you get the hang of how to create the pagination effect, check this tutorial

How to paginate with PHP?

May 13, 2012

Make counter in sql query

Question by Viktors Golubevs

I am using codeigniter and have working query which take user 3 images. I want to make a count an give every image a number 1,2,3,4,5,6,7 … and want that query output

-number (count),
-id,
-image,
-date

my sql query :

function bar_images_first($user_id)
{   
    $sql = "SELECT id, image, UNIX_TIMESTAMP(date) as date FROM images WHERE user_id = 3 LIMIT 3";
    $query = $this->db->query($sql, $user_id);
    return $query->result_array();
}

Is it possible to do counter in query?

Answer by Starx

It is possible by setting a SQL parameter as

SET @cnt := 0;
SELECT
    @cnt := @cnt + 1,
    id,
    image,
    UNIX_TIMESTAMP(date) as date 
FROM images WHERE user_id = 3 LIMIT 3";

But such multiple statements cannot be executed from the PHP’s mysql_query() method. But, mysqli function like mysqli_multi_query() does allow to execute multiple queries too, so if possible use mysqli method rather than the AR methods.

However, you can run multiple sets of query one by one.

$query = array(
    "SET @cnt := 0;",
    "SELECT
        @cnt := @cnt + 1,
        id,
        image,
        UNIX_TIMESTAMP(date) as date 
    FROM images WHERE user_id = 3 LIMIT 3"
);

foreach($query as $qry) {
     $result= $this->db->query($qry);
     //.........
}
May 7, 2012

What is wrong with this query? Why are results different locally?

Question by StackAttack

I am writing a php site using a mysql database for a class of mine and cannot for the life of me figure out what is wrong with it. I have a query that works locally on my machine (on an identical db to the one on the teacher’s server) but when I upload it, it doesn’t work. The problem is that the query is returning 0 results even though the db has info in it that should be showing.

function bigAssQuery($whereCondition)
{

    $queries[] = 'CREATE TEMPORARY TABLE subAssignments (SELECT ua.assignmentid, ua.assignmentnum, ua.description
                    FROM Course c JOIN UserAssignment ua ON ua.crn = c.CRN AND ua.term = c.term
                    WHERE c.CRN = "'.$_SESSION["crnum"].'" AND c.term = "'.$_SESSION["mysem"].'")';

    $queries[] = 'CREATE TEMPORARY TABLE subStudents (SELECT s.studentid, s.lastname, s.firstname
                    FROM Course c JOIN Student s ON s.crn = c.CRN AND s.term = c.term
                    WHERE c.CRN = "'.$_SESSION["crnum"].'" AND c.term = "'.$_SESSION["mysem"].'")';

    $queries[] = 'CREATE TEMPORARY TABLE subRubric(SELECT assignmentid, re.rubricelementid, re.learning_goal_char
                    FROM RubricElement re JOIN RubricAssignmentRelation rar ON re.rubricelementid = rar.rubricelementid)';

    $queries[] = 'CREATE TEMPORARY TABLE subAssignRub(SELECT subAssignments.assignmentid, rubricelementid, learning_goal_char, assignmentnum, description
                    FROM subRubric JOIN subAssignments ON subAssignments.assignmentid = subRubric.assignmentid)';

    $queries[] = 'CREATE TEMPORARY TABLE subAssignRubStud (SELECT *
                    FROM subAssignRub CROSS JOIN subStudents)';

    $queries[] = 'CREATE TEMPORARY TABLE subAssignInstRubStud (SELECT sars.assignmentid, ai.ainstanceid, rubricelementid, learning_goal_char, assignmentnum, description, sars.studentid, lastname, firstname
                    FROM subAssignRubStud sars LEFT JOIN AssignmentInstance ai ON sars.studentid = ai.studentid AND sars.assignmentid = ai.assignmentid)';

    $queries[] = 'CREATE TEMPORARY TABLE subTotal (SELECT assignmentid, siars.ainstanceid, s.ainstanceid As scoreAID, siars.rubricelementid, learning_goal_char, assignmentnum, description, studentid, lastname, firstname, score
                    FROM subAssignInstRubStud siars LEFT JOIN Score s ON siars.ainstanceid = s.ainstanceid AND siars.rubricelementid = s.rubricelementid
                    ORDER BY lastname, assignmentid)';

    $queries[] = 'SELECT *
        FROM subTotal
        '.$whereCondition.' Order By lastname, assignmentnum, learning_goal_char';  

    return($queries);
}

Then when the db is queried the code looks like this. . .

$queries = bigAssQuery($whereCondition);


$result = 1;
foreach($queries as $query)
{
    $result = $db->query($query);

    if(!$result)
    {
        echo '<script type="text/javascript"> 
                window.onload=function(){ alert("Error: Could not extract course information. Please try again later."); } 
             </script> ';
        exit;
    }
}

$num_rows = $result->num_rows;

I assure you that the local and remote databases are identical. I see no reason why no results are coming back. I did test a few simple temp tables to see if the server wasn’t reading those tables for some reason, but they weren’t an issue in my tests. I would try with nested subqueries, but it gets so convoluted so quickly that I can’t organize it. Maybe there is a better way?
Also, just to clarify the queries aren’t failing, they just aren’t returning anything when I know that they should.
I apologize for the wall of text, but any help is appreciated.

EDIT: I really don’t know which of the queries the problem lies. I do know that I’m probably missing some important information. Part of that lies in my web inexperience. I test locally first because I’ve got the debugger working, but I honestly don’t know how to do remote debugging. I’m using netbeans and xdebug. If someone could suggest a how to get remote debugging set up I would probably be able to come up with some better data. Any suggestions would be helpful

EDIT AGAIN: Found the problem. Embarrassingly enough it was an error in data entry; one of my foreign keys was incorrectly entered. Thanks everybody for pointing me in the right direction.

Answer by Starx

On having a quick look, your code is stoping the execution of the PHP inappropriately. You should at least the let the remainder to continue. Simply exit out of loop using break; instead.

if(!$result)
{
    echo '<script type="text/javascript"> 
            window.onload=function(){ alert("Error: Could not extract course information. Please try again later."); } 
         </script> ';
    break; //exit the loop only NOT THE PHP's Execution
}

Furthermore, check every query individually and run them separately on phpMyAdmin to see, if they are executing correctly. Find the break point and fix the error.

May 3, 2012

how to form select query by range

Question by bonny

hello i have a search engine for my site. i have two selection fields. i would like to give an example:

input_a: 3
input_b: 5

so the queries should look like:

if ( ($input_a == true) && ($input_b == false) ){
        $filter_orders[] = " `col` LIKE '%$input_a%' ";
    } 
    if ( ($input_a == false) && ($input_b == true) ){
        $filter_orders[] = " `col` LIKE '%$input_b%' ";
    } 
    if ( ($input_a == true) && ($input_b == true) ){
        $filter_orders[] = " `col`= `col`>='%$input_a%' AND `col` = `col`<='%$input_b%' ";

now the problem is, that i dont know, if the last query is incorrect or not. the logic behind that will be that in case of my example the range between 3 and 5 should be found.

so 1,2 [3,4,5] 6,7,8…

if there is someone who could help me out i really would appreciate.

thanks a lot.

Answer by Starx

NO, sadly that is everything but correct. It should be something like this.

$filter_orders[] = " `col`>='%$input_a%' AND `col`<='%$input_b%' ";
April 23, 2012

how to change variable when filter is on

Question by bonny

hello i would like to code a filter for an search engine on my site. now i thought it would be the best way to use a simple filter. so my code looks like:

}else if ($filter_a === 1){
        $filter = "WHERE `a` LIKE '%$a%'";
}else if ($filter_b === 1){
        $filter = "WHERE `b` LIKE '%$b%'";
}else if ($filter_c === 1){
        $filter = "WHERE `c` LIKE '%$c%'";
...
}else if ( ($filter_a === 1) && ($filter_b === 1) ){
        $filter = "WHERE `a` LIKE '%$a%' AND `b` LIKE '%$b%'";
}else if ( ($filter_a === 1) && ($filter_c === 1) ){
        $filter = "WHERE `a` LIKE '%$a%' AND `c` LIKE '%$c%'";
... and so on

the problem is that i have 5x N so the permutation of this would be 120 possibilites for $filter

is there a way how i can solve this? if there is someone who could help me out i really would appreciate. thanks a lot.

Answer by Starx

How about this way?

if ($filter_a === 1) {
        $filter[] = "`a` LIKE '%$a%'";
}
if ($filter_b === 1) {
        $filter[] = "`b` LIKE '%$b%'";
}
if ($filter_c === 1) {
        $filter[] = "`c` LIKE '%$c%'";
}
$filter = "WHERE ".implode(" AND ", $filter);
April 15, 2012

AND condition not working in MySQL Query

Question by dotman14

Please i need your help with my script. Whenever i include

AND maintable.semester_name = '$semester_name'

in the MySQL Query, it returns 0 for both values of semester_name, when actually
only one is supposed to have a value of 0 when echo $nums is processed. When i
remove

AND maintable.semester_name = '$semester_name'

the query gives normal results as i expect.

Thanks.

$query = "SELECT *             
   FROM maintable 
   WHERE maintable.matric_no = '$matric_no'
   AND   maintable.session = '$session'
   AND   maintable.semester_name = '$semester_name'
   AND   maintable.level = '$level'";
$result = mysql_query($query);
$nums = mysql_numrows($result);
echo $nums ;                  

TABLE STRUCTURE

COURSES
  course_id int(100) 
  course_code varchar(100) 
  course_title varchar(100) 
  course_unit int(10) 

MAINTABLE
  maintable_id int(255) 
  matric_no int(10) 
  session varchar(10) 
  semester_name varchar(10) 
  course_code varchar(10) 
  level int(10) 
  score int(10) 
  grade varchar(4) 

RESULT_UPLOAD
  upload_id int(10) 
  session varchar(10) 
  semester_name  varchar(10) 
  course_code varchar(10) 
  level varchar(10) 

SEMESTER
  semester_id int(10) 
  semester_name varchar(10) 

STUDENT
  matric_no int(10) 
  first_name varchar(100) 
  last_name varchar(100) 
  other_name varchar(100) 
  level int(10) 

USERS
  users_id int(10) 
  title varchar(20) 
  first_name varchar(20) 
  last_name varchar(20) 
  username varchar(20) 
  password varchar(100) 
  register_date datetime 
  tmp_name varchar(100) 
  type varchar(20) 
  name varchar(20) 
  size int(10) 

YEAR
  level_id int(10) 
  level int(10) 

Answer by Andreas Linden

The query is correct. I guess your conditions really don’t match any rows in the table. Just check what values you have in the database and what your are passing into the conditions.

Answer by Starx

Your query is correct, but with bit of ambiguity. The following query is run as exactly you are doing, but with less words.

$query = "SELECT *             
   FROM maintable 
   WHERE matric_no = '$matric_no'
   AND   session = '$session'
   AND   semester_name = '$semester_name'
   AND   level = '$level'";

Now, about your problem, the only way this might be happening may be due to no record is matching the records in your database.

Try to get the query you are running with echo $query and run the query directly from phpmyadmin to see how many result set you will get.

April 14, 2012

Data ordering issue in MYSQL

Question by Lewis Wheeler

got a small little problem I’m hoping someone can help me with.

I have the following dataset in MYSQL:

SELECT * FROM account;

pk  |  customer
1   |   1
2   |   0
3   |   1

I only need the customer column BUT I need it to be in the same order as above e.g.:

customer
1
0
1

However whenever I try and perform the following command, I get the following:

SELECT customer FROM account

customer
0
1
1

I have already tried the following with no luck:

SET @rownum=0;
SELECT @rownum:=@rownum+1 as rank, customer FROM account

rank |  customer
1    |   0
2    |   1
3    |   1

UPDATE: I forgot to add something important. I can’t rely on ordering by the primary key, mainly because the primary key could be a varchar and not integer in some circumstances.

I need the order in which the data was inserted in the database. When I do the same query returning varchar values it is in the correct order.

Any ideas?

Answer: SQL query no order by question

Answer by Evan Mulawski

SELECT customer FROM account ORDER BY pk ASC

Answer by Starx

You can define the order without selecting that column. So, use this

SELECT `customer` from `account` ORDER BY `pk` ASC
...

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