INserting

[netmastan]

hi, I have imported data (from text file) into four fields(volume,book,number,hadith). Hadith fields has some space and line break like this

Narrated Ibn 'Umar: 

xxxxxxxxxxxxxxxxxxxxxxxxxx:


1. xxxxxxxxxxxxxxxxxxxxxxxxxxx. 

2. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx. 

3. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx . 

4.xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 

5. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

when I want to insert this clean fields hadith fields(after using hadith=nl2br(hadith), it does not insert. All other fields insert correctly.

my code :

<?php



require_once("mainfile.php");
global $dbi;


$result=mysql_query("SELECT volume,book,number,hadith from hadith",$dbi);
while(list($volume,$book,$number,$hadith)=mysql_fetch_array($result)){
$hadith=str_replace("Narrated","Narrated&nbsp;",$hadith);
$hadith = nl2br($hadith);

$number=preg_replace("([^0-9.])", "", $number);
$volume = preg_replace("([^0-9.])", "", $volume);
$book = preg_replace("([^0-9.])", "", $book);


echo" Volume-$volume,Book-$book,Number-$number<p>$hadith<hr>";

$hadithx=mysql_query("INSERT into hadithx(id,volume,book,number,hadith) values('','$volume','$book','$number','$hadith')",$dbi);

}

?>

[planetsim]

change this line
$hadithx=mysql_query("INSERT into hadithx(id,volume,book,number,hadith) values('','$volume','$book','$number','$hadith')",$dbi);

to this
mysql_query("INSERT into hadithx(id,volume,book,number,hadith) values('','$volume','$book','$number','$hadith')");

It should work then

[Weedpacket]

Should you be trying to give id a null value? If it's an auto-incremented thing, then you don't need to mention it at all when you make the insert.

[planetsim]

good thing there Weedpacket didnt actually notice that until you mentioned it

[netmastan]

It does not work.... it just insert few records..and i have noticed that hadith field is giving problem.

Table Defination is : id(auto_increment),volume(varchar(30),book(varchar30),number(varchar40),hadith(Text)

So I just want to upate volume,book,number upating..

<?php



require_once("mainfile.php");
global $dbi;


echo " Testing ";


$result=mysql_query("SELECT volume,book,number,hadith from hadith ",$dbi);
while(list($volume,$book,$number,$hadith)=mysql_fetch_array($result)){
$hadith=str_replace("Narrated","Narrated&nbsp;",$hadith);
$hadith = nl2br($hadith);

$number=preg_replace("([^0-9.])", "", $number);
$volume = preg_replace("([^0-9.])", "", $volume);
$book = preg_replace("([^0-9.])", "", $book);

echo" #Volume-$volume,Book-$book,Number-$number<p><b>$hadith</b><hr>";

#$hadithx=mysql_query("INSERT into hadithx(id,volume,book,number,hadith) values('','$volume','$book','$number','$hadith')");
$result=mysql_query("update hadithz set number='$number',volume='$volume', book='$book'", $dbi) or die ('Failed');

}


?>

it update but volume,number and book has same value 1 for 7000 rows ??? Also Error at line(13)

while(list($volume,$book,$number,$hadith)=mysql_fetch_array($result)){

[Code_guy]

you don't have a where clause in your update query, so each time it is called all rows are updated

since you have an id field use:

$result=mysql_query("SELECT id,volume,book,number,hadith from hadith",$dbi);

and:

$result=mysql_query("update hadithz set number='$number',volume='$volume', book='$book' WHERE id=$id", $dbi) or die ('Failed');

[Code_guy]

if you want the instert statment to work, try:

$hadith = str_replace(("\r", "\n"), ("\\r", "\\n"), $hadith);

...

$hadithx=mysql_query("INSERT into hadithx(id,volume,book,number,hadith) values($id,'$volume','$book','$number','$hadith')");

nl2br only inserts '<br/>' before the linebreaks

using id in the instert statment is not necessary if you use autoincrement, but it makes the id's match the original table

[Weedpacket]

Okay, might hadith contain ' marks? Those would mangle the query because MySQL would misinterpret them as the end of the field and then get upset by the rest of the hadith. If that's the case then running it through mysql_escapestring() will make it ready for sticking in the query string.

To examine such things, instead of just dying with 'Failed', try using mysql_error() there to see what MySQL's problem with the query is.

[netmastan]

No luck sir , I have tried all the possible way... as u said.

I would be appreciated if anyone can have a look some the dump of the database(Just 20 row)

I have load 7000 records from text file.

What I want to keep numbers only on volume,book,number fields and hadith field should use line break so it looks nice when there is more then two paragrap or quote.

the code I am looking for clean fields and update the fields/

Thanks

[Weedpacket]

What is the precise error reported? Especially since I note that you're saying that it occurs while you're fetching from the original array.

[netmastan]

ok..sorry .

Note : All my datas are in the database . I want to clean up fields
(ie.number filed should only keep numbers) so I decided to clean up and update these fields.When I wan to update. it update all the fields(volume,number,book) (7000 rows) but shows 1 for all these fields. And it tell me error at

Warning: Supplied argument is not a valid MySQL result resource in c:\www\apache\htdocs\hadithcheck.php on line 13
// line 13 is
 while(list($id,$volume,$book,$number,$hadith)=mysql_fetch_array($result)){

INserting : NO error.. it escape some fields . I have checkd which field is escaping. It's hadith field. See the dump(very small file) ..it escape the first records. And there are more fields escaping.

Updating: As Insering giving me error ..I decided to leave it as it is.Cause...When I want to display hadith field simpli I can use nl2br. As I will add search funtion (search by volume or number etc). So I must clean at least these fields(Volume,Hadith and NUmber).

I am sorry if i could not make sense

thanks in advance.

[Weedpacket]

The thing is, it's saying that your $result is the problem - it's not getting as far as the insertion.

So there's something wrong with the resultset you've got in $result.

In some of youre code samples, you're using $result inside the loop for a second purpose; that would overwrite the result set you're trying to read.

Are you saying that this script gets part way through the job, inserts cleaned-up versions of some of the records, and then fails because "$result is not a valid resource"?

Here's a suggestion: replace the two lines:

$result=mysql_query("SELECT volume,book,number,hadith from hadith ",$dbi);
while(list($volume,$book,$number,$hadith)=mysql_fetch_array($result)){

with

$result=mysql_query("SELECT volume,book,number,hadith from hadith ",$dbi)
        or die(mysql_error());
$result_count=mysql_num_rows();
if(!$result_count)
{ echo "No records were retrieved from the table";
}
else
  for($i=0;$i<$result_count; ++$i)
  { list($volume,$book,$number,$hadith)=mysql_fetch_row($result);

As for the difference between updating and inserting; I'd stay with insertion; by making the volume, book, and number fields in the table numeric types instead of text, it will make sorting them easier, (and if you attach indexes, speed up selection). It will be easier to do this by inserting into a new table and then replacing the old table with the new one, than it would be by creating new columns with the appropriate types, filling in those new columns, and then dropping the old columns.

[Code_guy]

This worked for me with the hadith.txt table you posted:

$result=mysql_query("SELECT id,volume,book,number,hadith from hadith ",$dbi);
if (!$result) echo (mysql_error());
 while(list($id,$volume,$book,$number,$hadith)=mysql_fetch_array($result)){
$hadith=str_replace("Narrated","Narrated ",$hadith);
$hadith = nl2br($hadith);
echo" #Volume-$volume,Book-$book,Number-$number<br><b>$hadith</b><hr>";
$hadith = str_replace(array("\n", "\r", "'"), array("\\n", "\\r", "\\'"), $hadith);

$number=preg_replace("([^0-9.])", "", $number);
$volume = preg_replace("([^0-9.])", "", $volume);
$book = preg_replace("([^0-9.])", "", $book);


$hadithx=mysql_query("INSERT into hadithx(id,volume,book,number,hadith) values($id,'$volume','$book','$number','$hadith')");
if (!$hadithx) echo (mysql_error());

}

[Code_guy]

btw this works too:

$result=mysql_query("update hadithx set number='$number',volume='$volume', book='$book' WHERE id=$id", $dbi);

[netmastan]

Thank you guys..

Now it update volume,number and book. THe problem was in $result as weedpacket mentioned.

BUt Still I have the problem with hadith field. It escape inserting or updating some fields.

code_guy if you look carefully inserting result you will notice it

It start inserted from record 8 then record 12.... etc ..so record missing . I wonder what the difference between the first record and other record.

Anyway , thanks for ur time .... I think i can leave hadith field as it is... DO you think it will give problem when i do search this field. ..??

Cheers 🙂🙂 😃

[Code_guy]

The code as I posted it works perfectly.

The first $result=mysql_query ... statement was just fine.
It didn't produce any errors and the echo statment further down output all 20 records. It just needed to fetch the id column too.

The insert query didn't work because $hadith is replaced with the string it represents BEFORE the query is sent to mysql. Therefore a linefeed or (as Weedpacket pointed out) a single quote ' would cause errors.

The update query didn't work because you didn't have a where clause. When there is no where clause in an update query all records are updated. So it would update all 7000 recoreds each time you go through the loop. (7000 times?)

[Weedpacket]

Looking at the MySQL reference, I think you could probably update the
hadith field without PHP at all, if you have direct access to the MySQL DBMS.
Otherwise, you could of course send the following sort of queries via
mysql_query. Either way, there'll be no need for any of the data to be
passed from MySQL to PHP or back to risk getting mangled en route:

Turn \r\n into <br /> (emulate nl2br()

UPDATE hadith SET hadith=REPLACE(hadith, '\r\n', '<br />')

Turn the leading " Narrated" into " Narrated&nbsp;"

UPDATE hadith SET hadith=CONCAT(' Narrated ',SUBSTRING(hadith,10))

I don't see anything corresponding to preg_replace among MySQL's string
handling functions, however. But basically, it's using the database management system to manage the database.

[netmastan]

Thank u guys.. My database is clean now .

NOw i m trying to search in hadith filed. Use switch statment

switch($action) {

default:
index();
break;

case "search":
search($q);
break;

   }

so I can search this way >> index.php?action=serach&q=keywords.

But my search result displaying not match hadith(some match also when there keyword found in hadith) ?? Any hint ...

function search($q) {
    global $dbi;

$sql= sql_query("select id,volume,book,number,hadith from hadith where hadith like '%q%'  ", $dbi);
$result=mysql_num_rows($sql);
if(!$result)
	{
	echo " Sorry Nothing Found";
 	exit;
	}
	else
	{

	while(list($id,$volume,$book,$number,$hadith)=mysql_fetch_array($sql)){

	$hadith=str_replace(" Narrated '","Narrated  ",$hadith);

	$hadith = eregi_replace($q, "<span style=\"background-color: #FFFF00\"><b>$q</b></span>", $hadith);

	echo" #$id.Volume-$volume,Book-$book,Number-$number<br>$hadith<hr>";

	}

}

 }
[/php


Yahooo  I am nearly finishing the script.:D

[Weedpacket]

Just two points:

* %q% should be %$q%.

* This will only search for one word at a time; if multiple words are entered for searching on, it won't work. Also, it's case-sensitive - a search for 'peace' won't find any matches in your provided sample for example, even though it's there in hadith #5.

My guess is that what you'll really be wanting to use is a full-text search. MySQL offers this (I saw a guide to building and querying on full-text indexes in the MySQL documentation), but since I'm not entirely ofay with it, I won't be much help to you in setting it up.