...

View Full Version : Display mysql data problem



rts5678
02-16-2008, 04:47 AM
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.

oesxyl
02-16-2008, 05:15 AM
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?


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



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:



$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

oesxyl
02-16-2008, 06:27 AM
something like this. You must adjust it maybe, :)



<?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

Inigoesdr
02-16-2008, 04:01 PM
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:

$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.

rts5678
02-21-2008, 02:27 AM
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:

$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

Inigoesdr
02-21-2008, 03:31 PM
$lines = "$something";

That is overwriting your array of comments. Replace that line with
echo '<pre>' . print_r($lines, 1) . '</pre>';
And it should print the comments array in the correct order. If it is correct remove that line, and output the array:

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

rts5678
02-25-2008, 06:36 PM
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.

Inigoesdr
02-25-2008, 08:02 PM
Post an entire example file(a short one), and the code you're trying.

rts5678
02-28-2008, 05:37 AM
Here's the page with the 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($string, 0, $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&#37; 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 ($row= mysql_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();
?>

rts5678
02-28-2008, 05:41 AM
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 >>>>>>>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum