PDA

View Full Version : mysql


edbr
02-09-2006, 02:58 AM
Hi,
In file maker I can add values into a field seperated by commas, ie 1,2,3 and I am able to search by any of the values. Is this possible with mysql?
I have made this with three records however if I search with a different criteria, then i get all three records returned.
The field in question is a INT, but should I change this to text for search purposes????
thanks

davidj
02-09-2006, 08:38 AM
if you use mysql then i take it you will be using PHP

it would be easier to to get the whole contents of the field then explode the result into an array and then reference what you want
array[1] array[2] etc

mysql is powerful and there are string functions built in so you could (i think) return any part of your result if you just want to use mysql.

i would use php to do this

Creative Insanity
02-09-2006, 09:17 AM
I agree with dj on this one.

edbr
02-09-2006, 10:17 AM
I will read my dog eared 'mysql for complete morons' book tonight and see what I can do.
Thanks

edbr
02-20-2006, 02:32 AM
Well I couldnt get the result I wanted with arrays. I did get around the problem by making two search results page and adding an extra column to return the data I wanted.

i am sure this is a round about and suspect way of achieving it but it is working.
The answer of course is more learning and i vow on my next trip to Singapore or somewhere with a decent bookstore I will find a better book on PHP. sadly resources here in Bali are limited. Thanks you guys

davidj
02-20-2006, 08:48 AM
example...
$n = '1,2,3,4,5';
$v = explode(',',$n);

echo $v[0];

$n has hardcoded values which are delimited by a comma

$V has been defined with an explode(). First part of an explode is the delimiter in this case a comma then the var you want to perform the explode on

you can then reference any part of the ARRAY by just using...
$v[0] // array starts at 0
$v[1]
$v[2]
$v[3]

edbr
02-21-2006, 03:07 AM
Ok I got that, thanks.
I get bogged down with recordsets and am unsure how to apply some things, I am sure my next option must be a better understanding of PHP. DW is great but it is possible to get by without totally understanding the process.
However at least it has encouraged me to try and learn it.

davidj
02-21-2006, 01:23 PM
if you have a DO loop (repeat region) showing your database records then do this

inside your DO { loop add

$n = $row_Recordset['field'];
$v = explode(',',$n);

$row_Recordset['field']; is the recordset array column that contains your 1,2,3,4,5

you can reference any part of that array you want now using $v[0]; $v[1]; $v[2]; $v[3];

do you understand this?

edbr
02-22-2006, 02:25 AM
Yes amazingly!
I do get the explode array part but not the loop. here is the record set data.

edbr
02-22-2006, 02:26 AM
$maxRows_rs_bed = 10;
$pageNum_rs_bed = 0;
if (isset($_GET['pageNum_rs_bed'])) {
$pageNum_rs_bed = $_GET['pageNum_rs_bed'];
}
$startRow_rs_bed = $pageNum_rs_bed * $maxRows_rs_bed;

$colname_rs_bed = "1";
if (isset($_POST['bedroom'])) {
$colname_rs_bed = (get_magic_quotes_gpc()) ? $_POST['bedroom'] : addslashes($_POST['bedroom']);
}
mysql_select_db($database_villa, $villa);
$query_rs_bed = sprintf("SELECT location, description, image, bedroom, `view`, title, link, infobed FROM

villas WHERE bedroom = %s", $colname_rs_bed);
$query_limit_rs_bed = sprintf("%s LIMIT %d, %d", $query_rs_bed, $startRow_rs_bed, $maxRows_rs_bed);
$rs_bed = mysql_query($query_limit_rs_bed, $villa) or die(mysql_error());
$row_rs_bed = mysql_fetch_assoc($rs_bed);

if (isset($_GET['totalRows_rs_bed'])) {
$totalRows_rs_bed = $_GET['totalRows_rs_bed'];
} else {
$all_rs_bed = mysql_query($query_rs_bed);
$totalRows_rs_bed = mysql_num_rows($all_rs_bed);
}
$totalPages_rs_bed = ceil($totalRows_rs_bed/$maxRows_rs_bed)-1;
?>

davidj
02-22-2006, 08:51 AM
in the body of your page add the following and run the page

<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td>heading 1</td>
<td>heading 2</td>
<td>heading 3</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_rs_bed['*******']; ?></td>
<td><?php echo $row_rs_bed['*******']; ?></td>
<td><?php echo $row_rs_bed['*******']; ?></td>
</tr>
<?php } while ($row_rs_bed = mysql_fetch_assoc($rs_bed)); ?>
</table>

where i have ******* you need to insert your database column names

run this and tell me if it works. You should see all your db data cascading down

edbr
02-23-2006, 07:20 AM
I tried as you suggested and got an error message. It pointed to an error on a line which wasnt in the code (?)
I then created a new blank page added a record set and your code and it returned a message objecting to heading1,heading2,heading3

davidj
02-23-2006, 08:54 AM
post your entire code and include my code as you have written it

edbr
02-23-2006, 09:42 AM
whoops! i had another look and realised I had missed / deleted the final ?>
so now I got a result only one record showed but is that a problem?

anyway here is the code of the otherwise blank page as you requested.

edbr
02-23-2006, 09:43 AM
<?php require_once('Connections/villa.php'); ?>
<?php
$colname_rs_bed = "1";
if (isset($_POST['bedroom'])) {
$colname_rs_bed = (get_magic_quotes_gpc()) ? $_POST['bedroom'] : addslashes($_POST['bedroom']);
}
mysql_select_db($database_villa, $villa);
$query_rs_bed = sprintf("SELECT * FROM villas WHERE bedroom = %s", $colname_rs_bed);
$rs_bed = mysql_query($query_rs_bed, $villa) or die(mysql_error());
$row_rs_bed = mysql_fetch_assoc($rs_bed);
$totalRows_rs_bed = mysql_num_rows($rs_bed);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>

</head>


<body>

<table width="893" height="165" border="0" cellpadding="0" cellspacing="0">
<tr>
<th width="893" height="165" align="left" valign="bottom" scope="col"></th>
</tr>
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td>heading 1</td>
<td>heading 2</td>
<td>heading 3</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_rs_bed['location']; ?></td>
<td><?php echo $row_rs_bed['title']; ?></td>
<td><?php echo $row_rs_bed['view']; ?></td>
</tr>
<?php } while ($row_rs_bed = mysql_fetch_assoc($rs_bed)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($rs_bed);
?>
<?php

?>

davidj
02-23-2006, 10:00 AM
you have 2 <table> tags but only 1 close </table> tags

edbr
02-24-2006, 03:12 AM
I will have to put more water with it!
I took out a table so...


<body>

<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td>heading 1</td>
<td>heading 2</td>
<td>heading 3</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_rs_bed['location']; ?></td>
<td><?php echo $row_rs_bed['title']; ?></td>
<td><?php echo $row_rs_bed['view']; ?></td>
</tr>
<?php } while ($row_rs_bed = mysql_fetch_assoc($rs_bed)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($rs_bed);
?>
<?php

?>
I get one record displayed. Unfortunately I will be away for a few days but if you have a mind the site in question is www.concord-bali-villas.com.

Please use code tags when posting code

davidj
02-24-2006, 05:01 PM
you need to get this repeat region working before we can start playing with arrays

once you get that we can move onto the next bit.

you need to re-evaluate your design!
Your banner contains allot of artifacts due to the output level of your gradient

edbr
03-01-2006, 05:46 AM
Hi
Back in the office now.Ok I found my error which was I used the existing recordset and I had forgotten to remove the filter for form variable. All works as it should now.
Thanks for the comment on the banner, I had it on my 'to do' list for the next revision as I have to add quite a bit. However you shook me out of my lethargy. I wasn't sure how to lose the grainy appearance, I have replaced the Gif with a png image and its seems better.

davidj
03-01-2006, 10:32 AM
did you sort your problem out

edbr
03-02-2006, 01:12 AM
yes. As I said I had used the recordset I had before but had forgotten to remove the filter (form variable) so it was only returning one record. Now it returns all records.

davidj
03-02-2006, 11:08 AM
do you still need help with the array function i wrote or are you happy with what you have

edbr
03-03-2006, 03:29 AM
i think I would like to try and work it out.
However failing that I hope I could ask your assistance later???

davidj
03-03-2006, 09:12 AM
no problem