Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New to the CF scene
    Join Date
    Apr 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display mysql data problem

    Guys, I have spent a lot of time on this problem. Hopefully someone can help me.

    I am outputting data from a mysql table and one column is for customer comments. The column is named "message1". Data in that column is updated using CONCAT. In other words, each customer comment as part of an ongoing conversation is appended to the previous comment.

    I am storing timestamp with each comment so when comments are updated, I can display them like below :

    ------------------------------------
    2008-02-15 10:21:27 >>>>>>> I called to cancel my order, I left a voice message, so please credit my account back with $14.97. Thanks You >>>>>>>

    2008-02-15 10:25:33 >>>>>>> I left a message asking for credit, a response on my previous message will be appreciated>>>>>>>
    ------------------------------------

    So here's my problem.

    I want to display the data with the latest comment first. Because I am displaying only first 50 words on a webpage. I want the 'latest' 50 words displayed instead of the first 50.

    In othere words I want to be able to display the conversation on a webpage upside down with the latest comment displayed on top.

    How can I do that?
    Any help will be greatly appreciated.

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    this is mostly a mysql query problem, in my opinion.
    you could make your query to retrive timestamp and comment for a user order by timestamp and limit to how many rows you need.
    this with limits to a given number of words, in my opinion seems unnatural, why don't limit the number of comments in such way that don't exceed a given number of words or chars?

    Edit:
    I miss the part with concat, but you can count ends of line instead, \n, \r\n or <br>


    Edit:
    I messed all, I missunderstand how you store the data.
    I don't test that but could be fixed to work in case it don't:

    PHP Code:
    $comments explode(">>>>>>>",$commfrommysql);
    // sorry, I must test to see how it work, is harder I think first time, :)
    // I come back with a result, :) 



    best regards
    Last edited by oesxyl; 02-16-2008 at 05:28 AM.

  • #3
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    something like this. You must adjust it maybe,

    PHP Code:
    <?php

    $mysqlcomments 
    "2008-02-15 10:21:27 >>>>>>> I called to cancel my order, I left a voice message, so please credit my account back with $14.97. Thanks You >>>>>>>

    2008-02-15 10:25:33 >>>>>>> I left a message asking for credit, a response on my previous message will be appreciated>>>>>>>

    2008-02-15 10:21:27 >>>>>>> I called to cancel my order, I left a voice message, so please credit my account back with $14.97. Thanks You >>>>>>>

    2008-02-15 10:25:33 >>>>>>> I left a message asking for credit, a response on my previous message will be appreciated>>>>>>>"
    ;

    $comments = array();
    $commentarray explode(">>>>>>>",$mysqlcomments);
    $numcomm count($commentarray);
    foreach(
    $commentarray as $pos => $val){
      
    // this assume that each comment field in mysql start with timestamp
      // as in $mysqlcomments
      
    if($pos &#37; 2 == 0 && $pos < $numcomm && !empty($val)){
        
    $comments[$val] = $commentarray[$pos 1];
      }
    }
    // now sort by date descending, if is not well sorted you can use
    // a custom function
    ksort($comments);
    $charsnumber 0;
    foreach(
    $comments as $pos => $val){
      
    // you can replace this with something that count words instead of chars
      
    $carnumbers += strlen($pos) + strlen($val);
      if(
    $charnumbers 50){
        print 
    $pos.">>>>>>>".$val."<br>";
      }
    }

    ?>
    best regards

  • #4
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,638
    Thanks
    2
    Thanked 404 Times in 396 Posts
    This has no error checking, and makes a couple of assumptions(the comments are in order from oldest to newest, the input string contains the delimiter(">>>>>>>\r") at least once,
    and the last line ends with the delimiter, or the delimiter followed by a space or newline), but should give you the correct result:
    PHP Code:
    $content "2008-02-15 10:21:27 >>>>>>> I called to cancel my order, I left a voice message, so please credit my account back with $14.97. Thanks You >>>>>>>
    2008-02-15 10:25:33 >>>>>>> I left a message asking for credit, a response on my previous message will be appreciated>>>>>>>
    2008-02-16 10:25:33 >>>>>>> test message>>>>>>>"
    ;


    // array of the comments in order from newest to oldest; example:  "2008-02-16 10:25:33 >>>>>>> test message"
    $lines array_reverse(array_filter(array_map('trim'explode(">>>>>>>\r"rtrim($content) . "\r")))); 
    This method bypasses the need make an array of the dates and sort them.

  • #5
    New to the CF scene
    Join Date
    Apr 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Inigoesdr View Post
    This has no error checking, and makes a couple of assumptions(the comments are in order from oldest to newest, the input string contains the delimiter(">>>>>>>\r") at least once,
    and the last line ends with the delimiter, or the delimiter followed by a space or newline), but should give you the correct result:
    PHP Code:
    $content "2008-02-15 10:21:27 >>>>>>> I called to cancel my order, I left a voice message, so please credit my account back with $14.97. Thanks You >>>>>>>
    2008-02-15 10:25:33 >>>>>>> I left a message asking for credit, a response on my previous message will be appreciated>>>>>>>
    2008-02-16 10:25:33 >>>>>>> test message>>>>>>>"
    ;


    // array of the comments in order from newest to oldest; example:  "2008-02-16 10:25:33 >>>>>>> test message"
    $lines array_reverse(array_filter(array_map('trim'explode(">>>>>>>\r"rtrim($content) . "\r")))); 
    This method bypasses the need make an array of the dates and sort them.
    ok, I am having a problem implementing the above code.

    The way it currently works on the output page is that (as I mentioned before) message1 is the name of the column in which comments are stored.

    So this how I am trying to do it on the page where I am displaying the comments:


    // now you can display the results returned
    while ($row= mysql_fetch_array($result)) {

    $title6 = $row["name"];

    $content = $row["message1"];

    $lines = array_reverse(array_filter(array_map('trim', explode(">>>>>>>\r", rtrim($content) . "\r"))));

    $lines = "$something";


    When I try to echo $something, it displays nothing.

    What am I doing wrong?

    Before I implemented your code, I had
    $title7 = $row["message1"];

    and all I did was echo " $title7 ";
    and it displayed the comments albeit not in the order I wanted.

    What needs to be changed here? Thanks in advance

  • #6
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,638
    Thanks
    2
    Thanked 404 Times in 396 Posts
    Quote Originally Posted by rts5678 View Post
    $lines = "$something";
    That is overwriting your array of comments. Replace that line with
    PHP Code:
    echo '<pre>' print_r($lines1) . '</pre>'
    And it should print the comments array in the correct order. If it is correct remove that line, and output the array:
    PHP Code:
    foreach($lines as $line)
    {
         echo 
    $line '<br />';


  • #7
    New to the CF scene
    Join Date
    Apr 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi.

    In the output that I am receiving, the order is still the same. As in, the latest messages are showing at the bottom and the earliest ones on top.

    I want the latest ones showing on top so I can cut the output to a certain number of characters and still be able to glance at the latest comment.

    Here's what I am getting

    ==========================
    2008-02-23 13:26:38 >>>>>>> I can not find network - can not receive calls nor can sent anything. >>>>>>>

    2008-02-23 13:27:26 >>>>>>> please asap. >>>>>>>

    2008-02-25 11:33:05 >>>>>>> I need a RMA number >>>>>>>
    ==========================

    I need the comment with the latest timestamp showing first.

    Thanks for all the help so far.

  • #8
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,638
    Thanks
    2
    Thanked 404 Times in 396 Posts
    Post an entire example file(a short one), and the code you're trying.

  • #9
    New to the CF scene
    Join Date
    Apr 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here's the page with the code:
    PHP Code:
    <?

    $date 
    date"Y-m-d" );

    /* connection information */ 
    $hostname =  "localhost";
    $username =  "XXXXXXX";
    $password =  "XXXXXXX";
    $dbName =  "XXXXXX";
    /* make connection to database */ 
    MYSQL_CONNECT($hostname$username$password) OR DIE( "Unable to connect
    to database"
    );

    @
    mysql_select_db("$dbName") or die(  "Unable to select database"); 


    function 
    myTruncate($string$limit$break="."$pad="..."



    // return with no change if string is shorter than $limit  

    if(strlen($string) <= $limit) return $string

    // is $break present between $limit and the end of the string?  

    if(false !== ($breakpoint strpos($string$break$limit))) { 
    if(
    $breakpoint strlen($string) - 1) { 
    $string substr($string0$breakpoint) . $pad;
     } 

    return 
    $string;
    }

    /* set the allowed order by columns */
    $default_sort 'id';
    $allowed_order = array ('orderno','reason','date','name');

    /* if order is not set, or it is not in the allowed
     * list, then set it to a default value. Otherwise, 
     * set it to what was passed in. */
    if (!isset ($_GET['order']) || 
        !
    in_array ($_GET['order'], $allowed_order)) {
        
    $order $default_sort;
    } else {
        
    $order $_GET['order'];
    }

      
    // Get the search variable from URL
      
    $var = @$_GET['q'] ;
      
    $var2 = @$_GET['z'] ;

     
    $trimmed =  trim($var); //trim whitespace from the stored variable
     
    $trimmed2 trim($var2); //trim whitespace from the stored variable

    // rows to return
    $limit=5500


    //check if the starting row variable was passed in the URL or not
    if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow'])) {
      
    //we give the value of the starting row to 0 because nothing was found in URL
      
    $startrow 0;
    //otherwise we take the value from the URL
    } else {
      
    $startrow = (int)$_GET['startrow'];
    }

     
    $query "SELECT * from csm where action !='Order Cancelled' AND action !='Ticket Closed' AND reason !='wholesale lead' order by $order DESC LIMIT $startrow, 250";
     
    $numresults=mysql_query($query);
     
    $numrows=mysql_num_rows($numresults);

     

    $result mysql_query ($query)
    or die(
    "Query error: "mysql_error());


    if (
    $numrows == 0)
      {

    ?>
    <table width=1600>
    <tr><td><font face="arial" size="2">

    <?



      
    echo "<h4>Results</h4>";
      echo 
    "<p>No results between <SPAN CLASS='style'>$trimmed</SPAN> AND <SPAN CLASS='style'>$trimmed2</SPAN> of <SPAN CLASS='green'>$trimmed5</span> where <SPAN CLASS='blue'>Item Status</span> = <SPAN CLASS='style'>Backordered</SPAN> & Search Term = <SPAN CLASS='style'>$trimmed3</SPAN> were found.</p>";

    // google
     
    echo "<p><A HREF=\"javascript:history.go(-1)\">Click here to search again</A></p>";
      }


    echo 
    "
    <center><table width=1600>"
    ;

    // open a form
    print "<form name='namestoupdate' onsubmit='return autocheck(this)' method='post' action='update.php'>\n";

    echo 
    "

    <tr><td>The following links allow you to pull separately inquiries based on 'reason'</td></tr>
    <tr><td><a href=\"main.php\">Back to Main</a> | <a href=\"mainochange.php\">Order Change</a> | <a href=\"mainocancel.php\">Order Cancel</a> | <a href=\"mainphoneorder.php\">Phone Orders</a> | <a href=\"mainrefopen.php\">Refund Open</a> | <a href=\"mainrmarequest.php\">RMA Request</a> | <a href=\"mainaccessories.php\">Accessories Related</a> | <a href=\"maincreditcard.php\">Credit Card</a> | <a href=\"mainphoneprob.php\">Phone Problems</a>| <a href=\"mainphoneprob.php\">Phone Locked</a> | <b><center>$numrows tickets </center></td></tr>



    <tr><td><font face=\"arial\" size=\"2\">
    <table border=1 width=100% bordercolor=\"32688F\" style=\"border-collapse: collapse\">
    <tr>
    <td bgcolor=000000><font face=\"Arial, Helvetica\" size=\"2\" color=FFFFFF><b><a href=main.php?q=$trimmed&z=$trimmed2&c=$trimmed3&e=$trimmed5&order=orders_id><font color=FFFFFF>ID</font></a></b></td>
    <td bgcolor=000000 width=130><font face=\"Arial, Helvetica\" size=\"2\" color=FFFFFF><b><a href=main.php?q=$trimmed&z=$trimmed2&c=$trimmed3&e=$trimmed5&order=date><font color=FFFFFF>Date</font></a></b></td>
    <td bgcolor=000000><font face=\"Arial, Helvetica\" size=\"2\" color=FFFFFF><b><font color=FFFFFF>Email</font></b></td>
    <td bgcolor=000000><font face=\"Arial, Helvetica\" size=\"2\" color=FFFFFF><b><a href=main.php?q=$trimmed&z=$trimmed2&c=$trimmed3&e=$trimmed5&order=phone><font color=FFFFFF>Phone</b></a></td>
    <td bgcolor=000000><font face=\"Arial, Helvetica\" size=\"2\" color=FFFFFF><b><a href=main.php?q=$trimmed&z=$trimmed2&c=$trimmed3&e=$trimmed5&order=description><font color=FFFFFF>Name</b></a></td>
    <td bgcolor=000000 width=20><font face=\"Arial, Helvetica\" size=\"2\" color=FFFFFF><b><a href=main.php?q=$trimmed&z=$trimmed2&c=$trimmed3&e=$trimmed5&order=orderid><font color=FFFFFF>Order ID</font></b></td>
    <td bgcolor=000000 width=75><font face=\"Arial, Helvetica\" size=\"2\" color=FFFFFF><b><font color=FFFFFF><a href=main.php?q=$trimmed&z=$trimmed2&c=$trimmed3&e=$trimmed5&order=reason><font color=FFFFFF>Reason</font></a></font></b></td>
    <td bgcolor=000000 width=75><font face=\"Arial, Helvetica\" size=\"2\" color=FFFFFF><b><font color=FFFFFF>Action Taken</font></b></td>
    <td bgcolor=000000 width=75><font face=\"Arial, Helvetica\" size=\"2\" color=FFFFFF><b><font color=FFFFFF>Respond</font></b></td>
    <td bgcolor=000000 width=75><font face=\"Arial, Helvetica\" size=\"2\" color=FFFFFF><b><font color=FFFFFF>Notes</font></b></td>
    <td bgcolor=000000 width=75><font face=\"Arial, Helvetica\" size=\"2\" color=FFFFFF><b><font color=FFFFFF>Message</font></b></td>
    </tr>"
    ;


    // start a counter in order to number the input fields for each record
    $i 0;




    // now you can display the results returned
      
    while ($rowmysql_fetch_array($result)) {





      
    $title6 $row["name"];


      
    $title10 $row["email"];



      
    $title15 $row["arriving"];

      
    $title17 $row["item_count"];

    $content $row["message1"];


    $lines array_reverse(array_filter(array_map('trim'explode(">>>>>>>\r"rtrim($content) . "\r")))); 



    $title20 $row["orderno"];

    echo 
    "";


      print 
    "<input type='hidden' name='id[$i]' value='{$row['id']}' />";

      print 
    "<input type='hidden' name='orders_id[$i]' value='{$row['orders_id']}' />";
      print 
    "<input type='hidden' name='item_id[$i]' value='{$row['item_id']}' />";
      print 
    "<input type='hidden' name='description[$i]' value='{$row['description']}' />";

      print 
    "<tr><td><font size=2 face=arial><b><a href=\"https://66.124.30.249/order_management/start.php?value={$row['orderno']}&search_type=single%20order&view_orders=whole_order\" target=\"_blank\">{$row['id']}</a></td>

    <td><font size=2 face=arial>{$row['date']}</td>
    <td><font size=2 face=arial>{$row['email']}</td>
    <td><font size=2 face=arial>{$row['phone']}</td>

    <td><font size=2 face=arial>{$row['name']}</font></td>
    <td><font size=2 face=arial><a href=\"https://66.124.30.249/order_management/start.php?value={$row['orderno']}&search_type=single%20order&view_orders=whole_order\" target=\"_blank\">{$row['orderno']}</a></td>
    <td  bgcolor=FFFFCC><font size=-2 face=arial><b>{$row['reason']}</b></td>



    <td>
               <select name='action[$i]'>
                    <option value='{$row['action']}'>{$row['action']}</option>
                    <option value='Responded'>Responded</option>
                    <option value='Order Cancelled'>Order Cancelled</option>
                    <option value='Code Requested'>Code Requested</option>
                    <option value='Ticket Closed'>Ticket Closed</option>
                    <option value='Exchanged Item'>Exchanged Item</option>
                    <option value='Order Changed'>Order Changed</option>
                    <option value='RMA Sent'>RMA Sent</option>
                    <option value='Refund Given'>Refund Given</option>
                    <option value='Refund Open'>Refund Open</option>
                    <option value=''></option>
                  </select>
    </td>


    <td><font size=-2 face=arial><a href=\"email.php?id={$row['id']}&name={$row['name']}&email={$row['email']}\" onclick=\"window.open(this.href, 'popupwindow', 'width=800,height=800,scrollbars,resizable'); return false;\">Quick Response</a></td>




    <td><font size=-2 face=arial><a href=\"notes.php?id={$row['id']}\" onclick=\"window.open(this.href, 'popupwindow', 'width=800,height=800,scrollbars,resizable'); return false;\">Add Notes & Respond</a></td>



    <td><font size=-2 face=arial><b>"
    ;


    foreach(
    $lines as $line)
    {
         echo 
    $line '<br />';
    }  


    echo
    "</b></td>

    </tr></p>\n"
    ;

    // add 1 to the count, close the loop, close the form, and the mysql connection
    ++$i;
    }








    print 
    "<tr><td colspan=12><center><input type='submit' value='Submit'/></center></td></tr></table>";
    print 
    "</form>";

    echo 
    '<p><center><a href="'.$_SERVER['PHP_SELF'].'?startrow='.($startrow-250).'">Previous</a>&nbsp;&nbsp;&nbsp;&nbsp;<a href="'.$_SERVER['PHP_SELF'].'?startrow='.($startrow+250).'">Next</a></center>';
    mysql_close();
    ?>
    Last edited by Inigoesdr; 02-28-2008 at 01:45 PM. Reason: Use [php] tags for your code.

  • #10
    New to the CF scene
    Join Date
    Apr 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here's an example of the output page:

    In the first ticket Ticket 1028 there are 3 responses and all appear as oldest response first. I would like the newest response first:




    Ticket 1028 2008-02-27 2:30:45 theedwards@sbcglobal.net 12098621393 Tamara Edwards C152591 accessory Quick Response Add Notes & Respond 2008-02-27 2:30:45 >>>>>>> I got the wrong cases for my cell phone. I ordered 2 motorola W370 cases and my phone is a motorola W385. I tried calling a couple weeks ago but got no return. I would like to return the 2 cases I got and purchase 2 correct cases. >>>>>>>

    2008-02-27 14:25:53 >>>>>>> Hello, I have re-opened the order for you and I will make sure the correct items go out. The cherry one is out. If you like to select another item or wait for it I will be more then happy to assist. Thanks! >>>>>>>

    2008-02-27 22:15:50 >>>>>>> Thank you for getting back so fast. I love the cherry case so I will wait for it. How do I return the wrong cases to you, the W370 cases? I would like to get the MOTW385HPCLE001WP and the MOTW385HPCIM424NP cases for my W385, is that possible? >>>>>>>


    Ticket 1025 2008-02-26 23:36:23 Lachae_Jonesx23@raytheon.com 6014169640 Lachae Jones c155362 phone problem Quick Response Add Notes & Respond 2008-02-26 23:36:23 >>>>>>> Please advise status of phone- on backorder going on 2 months.I was told by cust.serv.3 weeks ago that it would ship the next week and the $ came out of my account on 1/22/08. And I paid $50.00 for overnight ship.I want to request a refund for the o.n. ship. and I was promised a free car charger >>>>>>>


  •  

    Posting Permissions

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