SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 26

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    HELP ME ON: Selecting all the data from one column of a table in mysql database

    Hello Everyone,
    May some one help me with sql statement and php on how to select all the data from one column of table. I have tried with a code below but it is not working. I want to be selecting some data (all) data from one column table that meets some condition, then use selected data to update other table.
    Php and sql are below;
    Your help will be highly appreciated.

    <?php

    // selecting all the records where uploaded is YES
    $query="SELECT * FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";
    $result=mysql_query($query);

    $num=mysql_numrows($result);
    mysql_close();

    $i=0;
    while ($i < $num) {
    // Getting the Travel numbers that meets the conditions, so that records
    //that that have same Travel number as selected can be updated using
    // update statement below.
    $zra=mysql_result($result,$i,"TravelNumber");

    $i++;
    }

    $mysql_query ="UPDATE traveldetails SET BatchNumber='$batchnumber'where uploaded='NO'AND TravelNumber='$zra";
    mysql_query($mysql_query) or die('Error, update query failed');




    ?>

  2. #2
    PHP Brainiac dg_den_golotyuk's Avatar
    Join Date
    Jul 2006
    Location
    Kiev, Ukraine
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is not a great problem. Try making in such a way:

    PHP Code:
    ...
    $res mysql_query("SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'");
    while ( 
    $item mysql_fetch_array($res) )
    {
        
    $current_travel_number $item['TravelNumber'];
    }

    ... 
    DG [Den Golotyuk], Lead Developer
    Chestnut Software
    Avoid web outsourcing scams!
    Click here
    for a free downloadable report

  3. #3
    PHP Brainiac dg_den_golotyuk's Avatar
    Join Date
    Jul 2006
    Location
    Kiev, Ukraine
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also the update statement you should move to the cycle body - cause you have to do updates for all rows, you are getting
    DG [Den Golotyuk], Lead Developer
    Chestnut Software
    Avoid web outsourcing scams!
    Click here
    for a free downloadable report

  4. #4
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello dg_den_golotyuk
    what do you mean by saying the update statement should move to the cycle body ?
    Give me some examples if it possible.
    thanks.

  5. #5
    PHP Brainiac dg_den_golotyuk's Avatar
    Join Date
    Jul 2006
    Location
    Kiev, Ukraine
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think, that your script will look like:

    PHP Code:
    <?php

    // selecting all the records where uploaded is YES
    $query="SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";
    $result=mysql_query($query);

    while (
    $item $mysql_fetch_array($result)) {
    $zra=itemp['TravelNumber'];

    if ( 
    $zra )
    {
    mysql_query("UPDATE traveldetails SET BatchNumber='$batchnumber' where uploaded='NO' AND TravelNumber='$zra'");
    }

    }

    ?>
    DG [Den Golotyuk], Lead Developer
    Chestnut Software
    Avoid web outsourcing scams!
    Click here
    for a free downloadable report

  6. #6
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have tried above code, it is not updating the other table (Traveldetails Table)

  7. #7
    PHP Brainiac dg_den_golotyuk's Avatar
    Join Date
    Jul 2006
    Location
    Kiev, Ukraine
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Think there is such DATA in theese tables, so there is nothing to update. Try to add a line before 'if ( $zra )': echo $zra; - will look what is going wrong
    DG [Den Golotyuk], Lead Developer
    Chestnut Software
    Avoid web outsourcing scams!
    Click here
    for a free downloadable report

  8. #8
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is not displaying records, but I can check from table that data is there.

  9. #9
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when I echo $zra; It is not displaying records, but I can check from table that data is there.

  10. #10
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Which means that the SELECT query is not returning any records. Probably because $batchnumber has no value?
    Ian Anderson
    www.siteguru.co.uk

  11. #11
    PHP Brainiac dg_den_golotyuk's Avatar
    Join Date
    Jul 2006
    Location
    Kiev, Ukraine
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru
    Which means that the SELECT query is not returning any records. Probably because $batchnumber has no value?
    You absolutely right!!! $batchnumber - what is this variable for?
    DG [Den Golotyuk], Lead Developer
    Chestnut Software
    Avoid web outsourcing scams!
    Click here
    for a free downloadable report

  12. #12
    PHP Brainiac dg_den_golotyuk's Avatar
    Join Date
    Jul 2006
    Location
    Kiev, Ukraine
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:

    <?php

    echo "SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";

    ?>
    DG [Den Golotyuk], Lead Developer
    Chestnut Software
    Avoid web outsourcing scams!
    Click here
    for a free downloadable report

  13. #13
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $batchnumber has value. becuase in the first table(payment) is showing that $batchnumber has value

  14. #14
    PHP Brainiac dg_den_golotyuk's Avatar
    Join Date
    Jul 2006
    Location
    Kiev, Ukraine
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So there are no rows, that are returning on such query.
    Insert such code after $res = mysql_query("SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'"):

    echo mysql_num_rows($res) + 0;

    It will show us the number of returned resulted rows
    DG [Den Golotyuk], Lead Developer
    Chestnut Software
    Avoid web outsourcing scams!
    Click here
    for a free downloadable report

  15. #15
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I use <?php

    echo "SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";

    ?>
    I get something similar or output in the broswer as shown below.
    SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='231'

  16. #16
    PHP Brainiac dg_den_golotyuk's Avatar
    Join Date
    Jul 2006
    Location
    Kiev, Ukraine
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by zamzam
    When I use <?php

    echo "SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";

    ?>
    I get something similar or output in the broswer as shown below.
    SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='231'
    so $batchnumber' has the value. That is not a problem. Try the feature with mysql_num_rows(...) now and post what is it outputting
    DG [Den Golotyuk], Lead Developer
    Chestnut Software
    Avoid web outsourcing scams!
    Click here
    for a free downloadable report

  17. #17
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I use mysql_num_rows(...) get the following out put 0

  18. #18
    PHP Brainiac dg_den_golotyuk's Avatar
    Join Date
    Jul 2006
    Location
    Kiev, Ukraine
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by zamzam
    When I use mysql_num_rows(...) get the following out put 0
    That means no rows matching your criteria with specified input parameters. I'm not actually getting what are you trying to do. The script i working fine - there are just no results on this query. Maybe you should try changing query somehow. Try to explain - what are you trying to do
    DG [Den Golotyuk], Lead Developer
    Chestnut Software
    Avoid web outsourcing scams!
    Click here
    for a free downloadable report

  19. #19
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have two tables. Payment and Travel details. I want when someone (User or admin) wants to generate a batch of records and then export data in csv. Iam able to generate a batch of records using payment table details with out a problem. Now the details that are in Traveldetails table and payment table are related. So what I want to achieve is like this. Since Iam generating a batch number which appended to the records in that batch using payment table, then I want same batch number to be appended to records with same Travel number in traveldetails table. So that is what I what to achieve. If you want some details iam reay to give. Your help will be highly appreciated.
    The code is below and table sql
    <?php
    include ('../conn.php');


    $ebatchh=mysql_query("select BatchNumber from payments where uploaded= 'YES' ORDER BY BatchNumber DESC LIMIT 1")or die("<center>
    <font face=\"arial\" size=\"2\">Error!! Select Operation Failed. Reason:
    ".mysql_error()."</font></center>");
    $row = mysql_fetch_object($ebatchh);
    $embbb= $row->BatchNumber;


    $batchnumber = $embbb+1;

    $mysql_query ="UPDATE payments SET BatchNumber='$batchnumber'where uploaded ='NO' ORDER BY Id ASC LIMIT 20";
    mysql_query($mysql_query) or die('Error, update query failed');



    $select = "SELECT EmployeeNumber , ApplicationDate,TravelNumber ,Transactioncode,TotalAmount,BatchNumber FROM payments where uploaded='NO' and BatchNumber='$batchnumber'";
    $export = mysql_query($select);
    $fields = mysql_num_fields($export);

    for ($i = 0; $i < $fields; $i++) {
    //$header .= mysql_field_name($export, $i) . "\t";
    }



    while($row = mysql_fetch_row($export)) {
    $line = '';
    foreach($row as $value) {
    if ((!isset($value)) OR ($value == "")) {
    $value = "\t";
    } else {
    $value = str_replace('"', '""', $value);
    $value = '' .$value. ',' . "\t";
    }
    $line .= $value;
    }
    $data .= trim($line)."\n";
    }
    $data = str_replace("\r","",$data);
    if ($data == "") {
    $data = "\n(0) Records Found!\n";
    }
    header("Cache-control: private");
    header("Content-type: application/force-download");
    header("Content-Disposition: attachment; filename=TRVP$batchnumber.csv");
    header("Pragma: no-cache");
    header("Expires: 0");
    print "$header\n$data";


    $mysql_query ="UPDATE payments SET uploaded='YES'where uploaded ='NO' ORDER BY Id ASC LIMIT 20";
    mysql_query($mysql_query) or die('Error, update query failed');


    //$ebtravel=mysql_query("select TravelNumber from payments where uploaded='YES' and BatchNumber ='$batchnumber'")or die("<center>
    // <font face=\"arial\" size=\"2\">Error!! Select Operation Failed. Reason:
    // ".mysql_error()."</font></center>");
    //$row = mysql_fetch_object($ebtravel);
    //$emtravel= $row->TravelNumber;

    //$res = mysql_query("SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'");
    //while ( $item = mysql_fetch_array($res) )

    // $current_travel_number = $item['TravelNumber'];












    $query="SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";
    $result=mysql_query($query);

    while ($item = $mysql_fetch_array($result)) {
    $zra=$item['TravelNumber'];

    if ($zra)
    echo "$zra";
    {
    mysql_query("UPDATE traveldetails SET BatchNumber='$batchnumber' where uploaded='NO' AND TravelNumber='$zra'");
    }

    }

    ?>


    SQL TABLE

    Table structure for table `payments`
    --

    CREATE TABLE `payments` (
    `Id` int(10) NOT NULL auto_increment,
    `EmployeeNumber` varchar(20) NOT NULL default '',
    `ApplicationDate` date NOT NULL default '0000-00-00',
    `TravelNumber` varchar(10) NOT NULL default '',
    `Transactioncode` varchar(30) NOT NULL default '',
    `TotalAmount` int(30) NOT NULL default '0',
    `BatchNumber` varchar(10) NOT NULL default '',
    `uploaded` varchar(3) default 'NO',
    PRIMARY KEY (`Id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

    --
    -- Dumping data for table `payments`
    --

    -- --------------------------------------------------------

    --
    -- Table structure for table `traveldetails`
    --

    CREATE TABLE `traveldetails` (
    `Id` int(10) NOT NULL auto_increment,
    `EmployeeNumber` varchar(10) NOT NULL default '',
    `ApplicationDate` date NOT NULL default '0000-00-00',
    `NRC` varchar(20) NOT NULL default '',
    `TravelNumber` varchar(20) NOT NULL default '',
    `SAPAccount` varchar(20) NOT NULL default '',
    `Description` text NOT NULL,
    `TransactionType` varchar(20) NOT NULL default '',
    `TotalAmount` int(20) NOT NULL default '0',
    `BatchNumber` int(20) NOT NULL default '0',
    `Payable` varchar(20) NOT NULL default '',
    `uploaded` varchar(3) NOT NULL default 'NO',
    PRIMARY KEY (`Id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

    --
    -- Dumping data for table `traveldetails`
    --

  20. #20
    PHP Brainiac dg_den_golotyuk's Avatar
    Join Date
    Jul 2006
    Location
    Kiev, Ukraine
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please add this code directly under include - in the top of the scipt. Run it and say, what does it outputs.

    $ebatchh=mysql_query("select BatchNumber from payments where uploaded= 'YES' ORDER BY BatchNumber DESC LIMIT 1");
    $row = mysql_fetch_object($ebatchh);
    echo $embbb= $row->BatchNumber;
    echo ' : ';
    echo $batchnumber = $embbb+1;

    $mysql_query ="SELECT * FROM payments WHERE uploaded ='NO' ORDER BY Id ASC LIMIT 20";
    $q = mysql_query($mysql_query);
    echo '<br> Number of rows mathcing you query: ';
    echo mysql_num_rows($q);
    DG [Den Golotyuk], Lead Developer
    Chestnut Software
    Avoid web outsourcing scams!
    Click here
    for a free downloadable report

  21. #21
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Below is the out put Iam getting

    226 : 227
    Number of rows mathcing you query: 7

  22. #22
    PHP Brainiac dg_den_golotyuk's Avatar
    Join Date
    Jul 2006
    Location
    Kiev, Ukraine
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    now please change '$query="SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";
    ' to 'echo " - " . $query="SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";
    '

    what will be the output?
    DG [Den Golotyuk], Lead Developer
    Chestnut Software
    Avoid web outsourcing scams!
    Click here
    for a free downloadable report

  23. #23
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the output when I use the above code

    Number of rows mathcing you query: 7 - SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='227'

  24. #24
    PHP Brainiac dg_den_golotyuk's Avatar
    Join Date
    Jul 2006
    Location
    Kiev, Ukraine
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let's now remake this block:
    PHP Code:
    $query="SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'"
    $result=mysql_query($query); 

    while (
    $item $mysql_fetch_array($result)) { 
    $zra=$item['TravelNumber']; 

    if (
    $zra)
    echo 
    "$zra"

    mysql_query("UPDATE traveldetails SET BatchNumber='$batchnumber' where uploaded='NO' AND TravelNumber='$zra'"); 



    change it to:

    PHP Code:
    $query="SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'"
    $result=mysql_query($query);

    echo 
    '<br> Total numbers:';
    echo 
    mysql_num_rows($result);
    echo 
    '<br>Iterating: <br>';

    while (
    $item $mysql_fetch_array($result)) { 
    echo 
    'zra = ' $zra=$item['TravelNumber'];

    if (
    $zra)
    echo 
    "$zra"

    mysql_query("UPDATE traveldetails SET BatchNumber='$batchnumber' where uploaded='NO' AND TravelNumber='$zra'"); 
    echo 
    ' ... this one updated successfully...';


    echo 
    '<br>'

    DG [Den Golotyuk], Lead Developer
    Chestnut Software
    Avoid web outsourcing scams!
    Click here
    for a free downloadable report

  25. #25
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Iam getting the output below but the script is not updating the Traveldetails table
    Total numbers:4
    Iterating


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •