Mysql quirey + String maniplulation HELP

[Virtual Yoda]

Hi All,

I was hoping to recive some help.  I need a DB quiery for a sales DB we have, i have the code (below) and it works as intended.

 

<?php
$db_host = '';
$db_user = '';
$db_pwd = '';

$database = '';
$table = 'nexus_purchases';

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// sending query
$result = mysql_query("Select ps_name,ps_member,ps_custom_fields from nexus_purchases where ps_name like 'Varified Owner'");

if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
echo "<table border='1'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td>$cell</td>";

    echo "</tr>\n";
}
mysql_free_result($result);
?>

 

 

The part i need help with is, this quirey returns,

 

 

Now the app that deals with our club shop, (nexus) adds bits to the DB I.e (  a:2:{i:3;s:8:  ) 

 

So in the two feilds when buying club membership someone enters  x  hae and ST24

 

 

And nexus adds  a:2:{i:3;s:8:"X HAE";i:4;s:4:"ST24";} to the table in ps_custom_fields

 

What i would like to do is strip out everything not in quotes and get left with  "X HAE"  "ST24"

 

I hope someone could help me out,  Thanks in Advance.

 

[gristoi]

you do know that the field in question is storing a serialized array? why would you want to mess with it. Can you give a bit more information on what you are trying to achieve?

[Virtual Yoda]

Thanks for the Reply

Short answer no i didnt,

 

in our Car club you buy membership online using IP.Nexus,

(we have set up custom feilds in the package to allow users to input there car details) as we need them for the events and show, (every time there is one)

 

At the moment because of Nexus, we can not veiw the information they place in the feilds with out veiwing the invoices one by one,  300+ , no thanks

Although we have put in a feature request, Who knows when and if it will happen,

 

The idea was to quirey the Db for the infomation we need,  its just we dont want to see it the extra,

 

(some of our events admin, are not computer literate, and these excess parts of the feild will just confuse things. )

 

Thanks again.

[AyKay47]

why not use unserialize to view the information in its proper format?

[gristoi]

The principle behind serialising the data is that you can unserialise it when you retrieve the data. a quick example of retrieveing the data in php and mysql is:

 

<?php
$result = mysql_query("Select ps_name,ps_member,ps_custom_fields from nexus_purchases where ps_name like 'Varified Owner'");
..... all of the standard stuff here

while($row = myql_fetch_array($result)
{
$ps_custom_fields_to_array = unserialize($row['ps_custom_fields']);
$ps_comma_seperated_string = implode(',',$ps_custom_fields_to_array);

// this will make $ps_comma_seperated_string look like X, HAE, ....
}

[Virtual Yoda]

Thank you, This is exacly what i needed,

BUT....    lol

 

I get a syntax error on line 22,

Which is

<?php
$result = mysql_query("Select ps_name,ps_member,ps_custom_fields from nexus_purchases where ps_name like 'Varified Owner'");
..... all of the standard stuff here

while($row = myql_fetch_array($result)
{     (syntax error here)
$ps_custom_fields_to_array = unserialize($row['ps_custom_fields']);
$ps_comma_seperated_string = implode(',',$ps_custom_fields_to_array);

// this will make $ps_comma_seperated_string look like X, HAE, ....
}

 

Did i break it, lol

 

Thanks Again.

 

AyKay47: Thank you for your answer, but it was a lil over my head,

 

[AyKay47]

what is the error and where is line 22..?

 

$row = myql_fetch_array($result)

 

here you forgot the s in mysql

[Virtual Yoda]

while($row = myql_fetch_array($result)
{     SYNTAX ERROR HERE 
$ps_custom_fields_to_array = unserialize($row['ps_custom_fields']);
$ps_comma_seperated_string = implode(',',$ps_custom_fields_to_array);

 

 

mySql

Oppsies,

Thanks

[AyKay47]

you are also missing a closing parenthesis.. line 21 should be

 

while($row = mysql_fetch_array($result))

[Virtual Yoda]

Thank you both of you,

 

The Syntax error has gone,  ) + the S, lol

 

But i fail, this is my first post

<?php
$db_host = '';
$db_user = '';
$db_pwd = '';

$database = '';
$table = 'nexus_purchases';

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// sending query
$result = mysql_query("Select ps_name,ps_member,ps_custom_fields from nexus_purchases where ps_name like 'Varified Owner'");

if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
echo "<table border='1'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td>$cell</td>";

    echo "</tr>\n";
}
mysql_free_result($result);
?>

 

This is your replies,

<?php
$result = mysql_query("Select ps_name,ps_member,ps_custom_fields from nexus_purchases where ps_name like 'Varified Owner'");
..... all of the standard stuff here

while($row = mysql_fetch_array($result))
{     (syntax error here)
$ps_custom_fields_to_array = unserialize($row['ps_custom_fields']);
$ps_comma_seperated_string = implode(',',$ps_custom_fields_to_array);

// this will make $ps_comma_seperated_string look like X, HAE, ....
}

 

 

Where do i put it?  i assumed i replaced the while in my code, with the while from your reply, But all i get is one cell thats empty?

 

Thank you so much guys(girls), i never would have got this, lol