MYSQL IN Clause error

Question 1

NewPHP’s Question:

I have used the below code in mysql query:

$all_PIDs=array();
foreach($pID as $p)
{
    $all_PIDs[]=$p->ID;
}
$AIDS=implode(',',$all_PIDs);
$table_tsk  = new Timesheets_Table_Tasks();
$select_tsk = $table_tsk->select()
            ->from($table_tsk, array
              (
                'Total'=>'SUM(timesheets_tasks.Time)',
                'Charged'=>'SUM(timesheets_tasks.Time_Charged)'
              ))
           ->where('timesheets_tasks.ProjectID IN ('.$AIDS.')')
            ;

But using the above code I am getting the following error:

“An error has occured
SQLSTATE[42000]: Syntax error or access violation: 1064 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 ‘))’ at line 1”

I have added a quotation mark(") for IN clause. But the problem is the query only displays for the first $AIDS number. Could someone help me to clear the error?
Thanks!

Answer

zerkms

It should be specified as:

->where('timesheets_tasks.ProjectID IN (?)', $all_PIDs)

so you’re passing an array of integers, not the comma-separated list of it

Answer

Starx

On your codes the quotes are not part of your MySQL query but only your PHP portion. DO this

$AIDS= "'".implode("','",$all_PIDs)."'";

And then

>where('timesheets_tasks.ProjectID IN ('.$AIDS.')'

Question 2

Question by paulyay

paulyay

I’m trying to import XML from this site http://data.gov.uk/dataset/car-parks to a phpmyadmin database, so I can use it in a google maps mashup.

I’m new to this and not sure how to go about getting the XML in to the database. Do I create the database columns first and then import the data?

Answer

Answer by Starx

Starx

There is a LOAD XML method in mysql, through which you can import the data from XML into your database.

An Example:

LOAD XML LOCAL INFILE '/pathtofile/file.xml' 
INTO TABLE `tablename` (fieldl1, field2, ...);

Question 3

Question by Abdullah Adam

Abdullah Adam

i want to alter table add column with the current date and hour like below its not adding column but when i remove variable and make it simple text its add and please also inform me how to make this column name unique so that we not able to add same column again in mysql again

    //get current date 
            $cname = date("Ymdh");
            echo "v1" . $cname ;

    //add curent date column if yet not added 
        mysql_query("ALTER TABLE  groups_ids add " . $cname . "VARCHAR(35)") ;

Answer

Answer by Starx

Starx

The problem is in the query you wrote. First you have escape the column name using a backtick (`), after that there is no space before the datatype

mysql_query("ALTER TABLE  groups_ids add `" . $cname . "` VARCHAR(35)") ;
                                                       //^ No space here

Question 4

Question by sico87

sico87

I have a CSV that looks like this,

candidate_id,show_on_site,first_name,surname,gender,DOB,showdob,Location,height,eyes,hair_colour,hair_length,accents,unions,training,url,visible,availability
,26,urban talent,Alice,Barry,Female,11 Jan 1942,FALSE,Manchester,5'2,Brown,Black,Mid-length,Native Lancashire,Equity,Urban Talent TV & Drama Workshops,Alice-Barry---5.4.06.jpg,Yes,Yes
,29,urban talent,Angela,Murray,Female,12 Oct 1970,FALSE,Manchester,5'7,Brown,Dark Brown,Long,"Native Manchester, others include - Cheshire, RP, Patois, Standard USA",Equity Member,"BA Acting Studies, Arden School of Theatre<br>Urban Talent TV & Drama Workshops",Angela Murray 1_1.jpg,Yes,Yes
,31,urban talent,Christine,Barton-Brown,Female,4 Jun 1947,FALSE,Manchester,5'0,Hazel,Blonde,Mid-length,"Native Manchester, others include - Liverpool, Cockney, Birmingham, West Country, Standard Scottish, Standard Welch, S Irish",,Manchester School of Acting<br>3 Years at David Johnson Acting Workshops,Christine Barton-Brown web 1.jpg,Yes,Yes
,33,urban talent,Davinia,Jokhi,Female,1 Jul 1979,FALSE,Manchester,5'2,Dark Brown,Dark Brown,Long,"Native Manchester, others include - Liverpool, RP, Lancashire, Birmingham, Cockney, Devon, Geordie, West Country, Glasgow, Edinburgh, South African, Standard & Southern US, Persian, Asian, Indian ~ good ear for accents",,"Manchester School of Acting, with Mark Hudson<br>North Cheshire Theatre College, with David Johnson<Oldham Theatre Workshop",Davinia Jokhi web 4.jpg,Yes,Yes

Is it possible to just insert this data into the existing columns in my database, all I can seem to it insert it as a new table which then has columns name A, B, C , D, E etc.

Answer

Answer by Starx

Starx

Using the LOAD DATA INFILE SQL statement you can import the CSV file, but to update you can’t. However, there is a trick you can use.

Create another temporary table to use for the import

Load onto this table from the CSC

LOAD DATA LOCAL INFILE '/file.csv'
INTO TABLE temp_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'n'
(field1, filed2, field3);

UPDATE the real table joining the table

UPDATE maintable
INNER JOIN temp_table A USING (field1)
SET maintable.field1 = temp_table.fiedl1

Question 5

Question by maanu

maanu

I need to select ids from database with arrays.
my english not good. I think the best way to show my codes

the form result print_r($malayalam); like this Array ( [0] => helo [1] => hi[2] => how)

I need to select its ids from table. my code is not correct. any way let me show you here

$results=mysql_query("SELECT ml_id FROM ml_table WHERE word = '$malayalam'"); 
$numrows=mysql_num_rows($results);
if($numrows!=0){
    $ml_row = mysql_fetch_array($results);
    $ml_id = $ml_row['ml_id'] ;
    echo "Malayalam ID " . $ml_id . "<br />";
}

I need to add all my result in to another array.

is that possible ?
if u have any idea could you answer to me please

Answer

Answer by maanu

maanu

finally fainally found solution with the help of answers

$rArray = mysql_query("SELECT ml_id FROM ml_table WHERE word IN ('".implode("', '", $malayalam)."')"); 
if(mysql_num_rows($rArray)>0){
    $temp_rows = array();       
    while(($row = mysql_fetch_array($rArray))) {
        $temp_rows[] = $row['ml_id'];
    }
}

the result of print_r($temp_rows) coming like this Array ( [0] => 123 [1] => 234 [2] => 312)

thank to all

Answer

Answer by Starx

Starx

If I understood properly, the following is what you need

$results=mysql_query("SELECT * FROM ml_table WHERE word = '$malayalam'"); 
if(mysql_num_rows($results)>0){

    $newArray = array(); //Create a new array

    while($ml_row = mysql_fetch_array($results)) {
        $ml_id = $ml_row['ml_id'] ;
        $newArray[$ml_id] = $ml_row;
        echo "Malayalam ID " . $ml_id . "<br />";
    }

    //$newArray is your new array accesible from id

}

Question 6

Question by dane rias

dane rias

<?php

require("phpsqlajax_dbinfo.php");
$dom  = new DOMDocument("1.0");
$dp   = fopen('samp.xml', 'w');
$node = $dom->createElement("Groceries");
fwrite($dp, '$node');
$parnode = $dom->appendChild($node);

$connection = mysql_connect($host, $user, $pass);
if (!$connection) {
    die('Not connected : ' . mysql_error());
}

$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
    die ('Can't use db : ' . mysql_error());
}

$query  = "SELECT * FROM tbl_groceryitem";
$result = mysql_query($query);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

echo "<groceries>";
while ($row = @mysql_fetch_assoc($result)) {

    $node = $dom->createElement("item");
    echo "<echo>";
    fwrite($dp, '$node');
    $newnode = $parnode->appendChild($node);
    $newnode->setAttribute("auto_id", $row['auto_id']);
    echo "<auto_id>", $row[auto_id];
    fwrite($dp, '$newnode');
    $newnode->setAttribute("Gro_barcode", $row['Gro_barcode']);
    echo "<Gro_barcode>", $row[Gro_barcode];
    fwrite($dp, '$newnode');
    $newnode->setAttribute("Gro_name", $row['Gro_name']);
    echo "<Gro_name>", $row[Gro_name];
    fwrite($dp, '$newnode');
    $newnode->setAttribute("Gro_brand", $row['Gro_brand']);
    echo "<Gro_brand>", $row[Gro_brand];
    fwrite($dp, '$newnode');
    $newnode->setAttribute("Gro_category", $row['Gro_category']);
    echo "<Gro_category>", $row[Gro_category];
    fwrite($dp, '$newnode');
    $newnode->setAttribute("Gro_aisle", $row['Gro_aisle']);
    echo "<Gro_category>", $row[Gro_aisle];
    fwrite($dp, '$newnode');
    $newnode->setAttribute("Gro_qty", $row['Gro_qty']);
    echo "<Gro_qty>", $row[Gro_qty];
    fwrite($dp, '$newnode');
    $newnode->setAttribute("Gro_netwt", $row['Gro_netwt']);
    echo "<Gro_netwt>", $row[Gro_netwt];
    fwrite($dp, '$newnode');
    $newnode->setAttribute("Gro_pic", $row['Gro_pic']);
    echo "<Gro_pic>", $row[Gro_pic];
    fwrite($dp, '$newnode');
    $newnode->setAttribute("Gro_price", $row['Gro_price']);
    echo "<Gro_price>", $row[Gro_price];
    fwrite($dp, '$newnode');
    $newnode->setAttribute("Gro_tax", $row['Gro_tax']);
    echo "<Gro_tax>", $row[Gro_tax];
    fwrite($dp, '$newnode');
    echo "</item>";
    fwrite($dp, '</item>');
}
fwrite($dp, '</groceries');
echo $dom->saveXML($xml);

?>

I’m new in php.

I’m creating a php file that can generate xml file with data from myPHPAdmin. Thanks :D. Hope someone can help me.

At first try, the code has been displayed in php and when I open to check for the created XML. the display was ‘$node‘, it reflects the exact string a place inside fwrite, when I try to delete the string quote (”) like this fwrite($dp,$node);. I got error.

And when I try to return the code to fwrite($dp,’$node’);. There was no Display. the XML page is blank.

Answer

Answer by Starx

Starx

You need to use XML headers when you are creating a XML file with PHP.

<?xml version="1.0" encoding="utf-8"?>

But this is not needed when you are using saveXML() on the end.

Here is an example, picked out from php’s manual.

<?php

$doc = new DOMDocument('1.0');
// we want a nice output
$doc->formatOutput = true;

$root = $doc->createElement('book');
$root = $doc->appendChild($root);

$title = $doc->createElement('title');
$title = $root->appendChild($title);

$text = $doc->createTextNode('This is the title');
$text = $title->appendChild($text);

echo "Saving all the document:n";
echo $doc->saveXML() . "n";

echo "Saving only the title part:n";
echo $doc->saveXML($title);
?>

Question 7

Question by Mark

Mark

I have 2 Tables in phpmyadmin that need joining

tracklisting is my one and catelogue is the other, and are saved as innodb

They both have a column CAT.NO and would like it to be joined on this column. In catelogue it is the primary and in tracklisting it’s indexed

catelogue is my parent and tracklisting would be the child as it doesn’t have info for every record in catelogue. I believe this would be correct unless I’m wrong

How do I do this so that when I query on a column in tracklisting it only brings up the matches for ‘catelogue’ because I want to know what album it’s on and not my entire 60000+ catelogue

Can this be done with phpmyadmin’s interface or is this a sql statement

Many thanks

EDIT:

This was the code that worked

SELECT *
FROM tracklisting
INNER JOIN catelogue ON catelogue.`CAT NO.` = tracklisting.`TRACKLISTING CAT NO.`
WHERE tracklisting.`ARTIST` LIKE 'placebo'

Thanks to everyone that helped out

Answer

Answer by Starx

Starx

I dont know if this can be done with the interface, but with sql

SELECT * 
FROM 
  tracklisting t 
  INNER JOIN catelouge c on c.catno=t.catno 
WHERE t.id = 1

Question 8

Question by sadi

sadi

record of
id  fare    commission  routecode   vehicle number  productcode date    time    driver  owner name
15  12345   123 4533    1   3344    2011-03-18  00:00:00    yasir   saleem
20  a   a   3433    1   2333    2011-03-25  00:00:00    yasir   saleem
36  11111   11111   3433    1   2333    2011-03-25  16:13:12    yasir   saleem
9   1233    123 3433    nk-234  2333    2011-03-24  00:00:00    siddiq  aslam
21  1200    120 4533    nk-234  7655    2011-03-24  00:00:00    siddiq  aslam
22  1200    133333  0987    nk-234  2333    2011-03-11  00:00:00    siddiq  aslam
23  10000   11  4533    nk-234  7655    2011-03-19  00:00:00    siddiq  aslam
25  122 12  0987    nk-234  2333    2011-03-11  00:00:00    siddiq  aslam
26  1000    100 3344    nk-234  7655    2011-03-11  00:00:00    siddiq  aslam
27  1000    100 3344    nk-234  2333    2011-03-10  00:00:00    siddiq  aslam
34  100 10  3344    nk-234  2333    2011-03-18  00:00:00    siddiq  aslam
35  100 10  3344    nk-234  2333    2011-03-02  00:00:00    siddiq  aslam
5   1000    100 1234    wq1233  3344    2011-03-10  22:30:00    waqas   sami
6   2222    22  1234    wq1233  3344    2011-03-17  22:30:00    waqas   sami
24  a   a   4533    PSS-1234    7655    2011-03-02  00:00:00    salman  salam
42633   145175

I want to add another column before id which counts the number of

rows. It should start from 1 and increment by 1 for each row.

Answer

Answer by RichardTheKiwi

RichardTheKiwi

If you mean in a SELECT statement:

Say your select was

select * from tbl

It becomes

select @n := @n + 1 RowNumber, t.*
from (select @n:=0) initvars, tbl t

Notes:

select @n:=0 is used to reset the global variable to 0
@n := @n + 1 increases it by 1 for each row, starting from 1. This column is named “RowNumber”

Answer

Answer by Starx

Starx

I am not sure if i understand your question completely, but to add a column infront of id run this query

ALTER TABLE `yourtablename` ADD `yournewfield` VARCHAR( 50 ) NOT NULL BEFORE `id`

Tag: phpmyadmin

MYSQL IN Clause error

NewPHP’s Question:

Importing XML in to phpmyadmin database

Question by paulyay

Answer by Starx

How to use current date variable to add new column in table?

Question by Abdullah Adam

Answer by Starx

importing a CSV into phpmyadmin

Question by sico87

Answer by Starx

php mysql select with array in to array

Question by maanu

Answer by maanu

Answer by Starx

Php that generates XML

Question by dane rias

Answer by Starx

How to make Table Joins in PHPmyAdmin

Question by Mark

Answer by Starx

how can I add a new column which counts the number of rows as serial number

Question by sadi

Answer by RichardTheKiwi

Answer by Starx

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