SitePoint Sponsor

User Tag List

Page 1 of 3 123 LastLast
Results 1 to 25 of 60

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Extracting a field

    Hello Friends,

    Please, I need help.

    Here's the partial codes of what I want to achieve:

    $result = mysql_query("SELECT * FROM tblGsm WHERE gsmtype ='$gsmtype'

    AND amount='$amount'");

    Table tblGsm has a third field called pin. I want to be able to extract

    the pin field based in the criteria of gsmtype and the amount choosen and

    then send the pin to the email address of the buyer and thereafter

    delete or move it from that record from the table.

    I would greatly appreciate any suggestions.

    Thank you.

  2. #2

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The result set referenced by $result already contains the PIN column. Do you want to delete all these rows or just empty the PIN column?

    In the former case
    PHP Code:
    mysql_query("DELETE FROM tblGsm WHERE gsmtype ='$gsmtype' AND amount='$amount'"); 
    in the latter
    PHP Code:
    mysql_query("UPDATE tblGsm SET pin='' WHERE gsmtype ='$gsmtype' AND amount='$amount'"); 

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello drzoid,

    I' d prefer to move the rows to a diferrent table. Is it possible? If not I'd want the rows deleted.

  4. #4

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Then have a look at this. However its rather a MySQL question than a PHP one, hence http://www.sitepoint.com/forums/forumdisplay.php?f=182 would be more appropriate.

  5. #5
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi drzoid,

    Lets assume I want to delete the row. This code does it:

    mysql_query("DELETE FROM tblGsm WHERE gsmtype ='$gsmtype' AND
    amount='$amount'");

    How do I now extract the pin field? I can handle the emailing part.

    Thank you.

  6. #6

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The value of your PIN column is already return by your initial SELECT query. The DELETE query here deletes all rows matching the WHERE clause.

    Its difficult to say more without knowing more about the database layout and the overall purpose.

  7. #7
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello drzoid,

    How do I make reference to the pin field? The pin value is unique for every record. I am trying to prevent the same pin number being sent to more than one buyer.

    I just need to know how to make reference the pin field after this:

    $result = mysql_query("SELECT * FROM tblGsm WHERE gsmtype ='$gsmtype'
    AND amount='$amount'");

    And send the PIN number of that pin field to the buyer. Its an eCommerce web site where registered members can buy GSM Recharge cards on line.

    Thanks.

  8. #8

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How many rows do you expect this SELECT query to return?

    Can you post your code?

  9. #9
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    drzoid,

    I only want one row, that's the pin field to be returned. Here is the code:

    <BODY background="images/ricebk.jpg">
    <center>
    <table width=600>
    <tr>
    <td width=500><font size=9 color="olive"><b><center>HelloMobile</center></b></font></td>
    </tr>
    </table>
    </center>
    <hr color = "olive" width=700>
    <?php
    include("dbconnect.inc.php");
    include("is_email_valid.php");
    $username = $_POST['username'];
    $password = $_POST['password'];
    $email = $_POST['email'];
    $phonenumber = $_POST['phonenumber'];
    $type = $_POST['elmType'];
    $amount = $_POST['elmAmounts'];
    if (empty($username))
    {
    echo "Please enter your User Name. It is required";
    exit;
    }
    if (empty($password))
    {
    echo "Please enter your Password. It is required";
    exit;
    }
    if (empty($email))
    {
    echo "Please enter your Email Address. It is required";
    exit;
    }
    if (empty($phonenumber))
    {
    echo "Please enter your Phone Number. It is required";
    exit;
    }
    if (empty($type))
    {
    echo "Please enter GSM Type. It is required";
    exit;
    }
    if (empty($amount))
    {
    echo "Please enter Amount. It is required";
    exit;
    }
    $pass = mysql_query("SELECT * FROM tblPass WHERE username='$username' AND password='$password'");
    while ($row=mysql_fetch_array($pass)){
    if(($row["password"]==$password) and ($row["username"]==$username)){
    $query = "insert into tblOrder
    (username, password, email, phonenumber, type, amount) values
    ('$username','$password','$email','$phonenumber','$type', '$amount')";
    mysql_query($query) or
    die(mysql_error());
    $result = mysql_query("SELECT * FROM tblGsm WHERE gsmtype ='$gsmtype'
    AND amount='$amount'");

    // Here is where I want the rest of the codes.

    echo "<b>Thank you for placing your order. Your entries are/b>:";
    echo "<p>";
    echo "<b>email/b> $email <br>";
    echo "<b>Phone Number/b> $phonenumber<br>";
    echo "<b>GSM type: </b>$type<br>";
    echo "<b>Amount: </b>$amount<br>";
    echo "<p>";
    echo "<b>Receipt of transaction has been sent to $email/b><br><? echo $email; ?>";
    $transdate = date("d F, Y g:i a");
    $mailTo = "$email, services@hellomobile.net";
    $subject = "Thank you for patronising us";
    $mailHeader = "From: $email";
    $message = "You bought $type worth $amount on the $transdate";
    mail($mailTo, $subject, $message, $mailHeader);
    ?>
    <p>
    <A HREF="index.htm">Click here to go to the Home Page</a>
    <?php
    exit;
    ?>
    <?PHP
    }
    else
    {
    }
    }
    ?>
    <b>Sorry, we don't have a member with that username and password in our records, <br>please try again and double check the data you entered.</b>
    <A HREF="order.htm">Try again</a>

  10. #10

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something along these lines should do what you want.
    PHP Code:
    $result mysql_query("SELECT pin FROM tblGsm WHERE gsmtype ='$gsmtype' AND amount='$amount'");
    if (
    mysql_num_rows($result)!=1) exit('SOMETHING WENT WRONG, THERE WERE LESS OR MORE THAN 1 ENTRY FOR THIS QUERY');

    $pin=mysql_result($result0);

    mysql_query('DELETE FROM tblGsm WHERE pin="'.mysql_escape_string($pin).'"'); 

  11. #11
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello drzoid,

    Just tried out the script. It gave me:

    SOMETHING WENT WRONG, THERE WERE LESS OR MORE THAN 1 ENTRY FOR THIS QUERY.

    The gsmtype and amount filed values are not unique. Its only the pin field that has to be unique. Here my table:

    sno gsmtype amount pin
    2 MTN 1500 123456
    3 VMobile 1000 698326
    4 VMobile 500 478994
    5 VMobile 1000 778894
    6 VMobile 500 874521
    7 MTN 1500 14789
    8 MTN 750 47896
    9 MTN 1500 58976

    Thank you.

  12. #12

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So you just want to use the first row matching gsmtype and amount? Then this should work
    PHP Code:
    $result mysql_query("SELECT pin FROM tblGsm WHERE gsmtype ='$gsmtype' AND amount='$amount' LIMIT 1");
    $pin=mysql_result($result0);

    mysql_query('DELETE FROM tblGsm WHERE pin='.$pin); 

  13. #13
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello drzoid,

    This time it says:

    Warning: mysql_result(): Unable to jump to row 0 on MySQL result index 6 in /home/hellomob/public_html/order.php on line 58

    What do I do?

    Thanks you.

  14. #14

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The call should give you the value of the first column of the returned data. Can you check in phpMyAdmin or another MySQL client whether your query is working as supposed?

  15. #15

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This will also tell you whether the query works.
    PHP Code:
    $result mysql_query("SELECT pin FROM tblGsm WHERE gsmtype ='$gsmtype' AND amount='$amount' LIMIT 1") or exit('Returned Error: '.mysql_error());
    $pin=mysql_result($result0);

    mysql_query('DELETE FROM tblGsm WHERE pin='.$pin); 

  16. #16
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello drzoid,

    Its still giving me:

    Warning: mysql_result(): Unable to jump to row 0 on MySQL result index 6 in /home/hellomob/public_html/order.php on line 58.

    I checked phpMyAdmin. It doesn't delete the row and I don't get a pin number along with the email sent.

    Thank you.

  17. #17

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, the SELECT statement is not supposed to delete anything. The point is, does your SELECT query work at all. Can you check that? What do you get when you run it through phpMyAdmin?

  18. #18
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello drzoid,
    Here is what I got when I ran it in phpmyAdmin:

    #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 '$result = mysql_query("SELECT pin FROM tblGsm WHERE gsmtype ='$

    Thank you.

  19. #19

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ehm , you certainly have to use real values in phpMyAdmin and cant use PHP variables

  20. #20
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello drzoid,

    How? I don't get you.

  21. #21

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT pin FROM tblGsm WHERE gsmtype='MTN' AND amount=1500
    for example

  22. #22
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello drzoid,

    I have other four gsmtypes: MTN, VMobile, MTel, Globacom. And each of this gsmtypes have more one amount. Check the site www.hellomobile.net/order.htm.

    How can one do this?

    Thank you.

  23. #23

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, I meant you should use these values just to check whether the query itself is working at all.

    Anyway, try the following and see whether you get any "Returned Error" message and how many rows are returned
    PHP Code:
    $result mysql_query("SELECT pin FROM tblGsm WHERE gsmtype ='$gsmtype' AND amount='$amount' LIMIT 1") or exit('Returned Error: '.mysql_error());
    echo 
    mysql_num_rows($result)." rows were returned for query 'SELECT pin FROM tblGsm WHERE gsmtype ='$gsmtype' AND amount='$amount' LIMIT 1'<br>";
    $pin=mysql_result($result0);

    mysql_query('DELETE FROM tblGsm WHERE pin='.$pin); 

  24. #24
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi drzoid,

    Same error. It beats me.

    Any way out?

    Thank you.

  25. #25

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by drzoid
    and see whether you get any "Returned Error" message and how many rows are returned


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
  •